How to create summary tables based on EnabledDate, EnabledDate + 1, EnabledDate + 2, EnabledDate + 7 with date filter option on power bi?

Question

I'm trying to create a summary tables base on 4 conditions on the date range

Input table

Date Set in text form

Date        Name       Id Count Screen  Enabled Date
03-01-2020  Karthikeyan 1   4   Login   03-01-2020
03-01-2020  Karthikeyan 1   3   Logout  03-01-2020
04-01-2020  Karthikeyan 1   5   Chat    03-01-2020
05-01-2020  Karthikeyan 1   6   Logout  03-01-2020
05-01-2020  Sumit       2   9   Login   05-01-2020
05-01-2020  Sumit       2   5   Logout  05-01-2020
06-01-2020  Karthikeyan 1   4   Login   03-01-2020
07-01-2020  Karthikeyan 1   5   Chat    03-01-2020
08-01-2020  Karthikeyan 1   6   Logout  03-01-2020
06-01-2020  Sumit       2   7   Login   05-01-2020
07-01-2020  Sumit       2   8   Chat    05-01-2020
08-01-2020  Sumit       2   9   Logout  05-01-2020
09-01-2020  Karthikeyan 1   4   Login   03-01-2020
10-01-2020  Karthikeyan 1   5   Chat    03-01-2020
11-01-2020  Karthikeyan 1   6   Logout  03-01-2020
09-01-2020  Sumit       2   4   Login   05-01-2020
10-01-2020  Sumit       2   5   Chat    05-01-2020
11-01-2020  Sumit       2   6   Logout  05-01-2020

Expected Output Tables

1) select Distinct id, name (Group by id, name)

2) select Total count with respect to date range

Date Range for all four table calculations

        (Enabled data) 
         03-01-2020     
         05-01-2020  

        (Enabled data) to  EnabledDate + 1
        StartDate          EndDate
        03-01-2020         04-01-2020      
        05-01-2020         06-01-2020       


       (Enabled data) to   EnabledDate + 2  
        StartDate          EndDate
        03-01-2020          05-01-2020      
        05-01-2020          07-01-2020      

       (Enabled data) to   EnabledDate + 7
        StartDate          EndDate
        03-01-2020         10-01-2020
        05-01-2020         11-01-2020(actual date is 12-01-2020 
but I don't have a value of end date so I consider the last date)

If Max date is not available for date range then consider the last date is an end date in the Date column.

3) select Max Screen views screen name base on the count, if two-screen have the same value then select the first screen

4) select Max Screen views count out of Total count

I'm have calculated Enabled Count in the following way it is producing the wrong result

EnabledDateCount = 
    ADDCOLUMNS(
        ADDCOLUMNS(
            FILTER(
                SUMMARIZE(
                    EnabledDate,
                    EnabledDate[Id],EnabledDate[Name],EnabledDate[Count]
                ),
                EnabledDate[Name] <> ""
            ),
            "TotalCount", CALCULATE( SUM( EnabledDate[Count] ),
            FILTER(EnabledDate,EnabledDate[Date] >= MIN(EnabledDate[Enabled Date]) 
            && EnabledDate[Date] <= MAX(EnabledDate[Enabled Date]) )
        ),
        "Screen",
        VAR CurrentCount = [Count]
        RETURN CALCULATE( MIN(EnabledDate[Screen]), EnabledDate[Count] = CurrentCount )
    ),"ScreenCount",  CALCULATE( MAX(EnabledDate[Count]),
    FILTER(EnabledDate,EnabledDate[Date] >= MIN(EnabledDate[Enabled Date]) 
    && EnabledDate[Date] <= MAX(EnabledDate[Enabled Date]) ) ))

Output is

But we need a distinct user id and name and the expected result is not correct

KARTHIKEYAN.A KARTHIKEYAN.A · Accepted Answer · 2020-01-12T14:46:47

Finally, I have created a solution following way

enabledDateSummary

enabledDateSummary = 
ADDCOLUMNS(
    ADDCOLUMNS(
    FILTER(
            SUMMARIZE(
                enabledDate,
                enabledDate[Id],
                enabledDate[Name], enabledDate[Enabled Date]
            ),enabledDate[Enabled Date] <> DATE(1970,01,01)
        ),
        "TotalCount", CALCULATE(SUMX(FILTER(enabledDate, 
        enabledDate[Date] = enabledDate[Enabled Date]),[Count])), 
        "ScreenCount", CALCULATE( MAX( enabledDate[Count] ) )
    ),
    "Screen",
    VAR CurrentCount = [ScreenCount]
    RETURN CALCULATE( MIN(enabledDate[Screen]), enabledDate[Count] = CurrentCount )
)

enabledDateSummary Output:

enabledDateSummary + 1

enabledDateSummary + 1 = 
ADDCOLUMNS(
    ADDCOLUMNS(
    FILTER(
            SUMMARIZE(
                enabledDate,
                enabledDate[Id],
                enabledDate[Name], enabledDate[Enabled Date]
            ), enabledDate[Enabled Date] <> DATE(1970,01,01)
        ),
        "TotalCount", CALCULATE(SUMX(FILTER(enabledDate, 
        enabledDate[Date] >= enabledDate[Enabled Date] && 
        enabledDate[Date] <= enabledDate[Enabled Date] + 1),[Count])),
        "ScreenCount", CALCULATE( MAX( enabledDate[Count] ) )
    ),
    "Screen",
    VAR CurrentCount = [ScreenCount]
    RETURN CALCULATE( MIN(enabledDate[Screen]), enabledDate[Count] = CurrentCount )
)

enabledDateSummary + 1 Output:

enabledDateSummary + 2

enabledDateSummary + 2 = 
ADDCOLUMNS(
    ADDCOLUMNS(
    FILTER(
            SUMMARIZE(
                enabledDate,
                enabledDate[Id],
                enabledDate[Name], enabledDate[Enabled Date]
            ), enabledDate[Enabled Date] <> DATE(1970,01,01)
        ),
        "TotalCount", CALCULATE(SUMX(FILTER(enabledDate, 
        enabledDate[Date] >= enabledDate[Enabled Date] && 
        enabledDate[Date] <= enabledDate[Enabled Date] + 2),[Count])),
        "ScreenCount", CALCULATE( MAX( enabledDate[Count] ) )
    ),
    "Screen",
    VAR CurrentCount = [ScreenCount]
    RETURN CALCULATE( MIN(enabledDate[Screen]), enabledDate[Count] = CurrentCount )
)

enabledDateSummary + 2 Output:

enabledDateSummary + 7

enabledDateSummary + 7 = 
ADDCOLUMNS(
    ADDCOLUMNS(
    FILTER(
            SUMMARIZE(
                enabledDate,
                enabledDate[Id],
                enabledDate[Name], enabledDate[Enabled Date]
            ), enabledDate[Enabled Date] <> DATE(1970,01,01)
        ),
        "TotalCount", CALCULATE(SUMX(FILTER(enabledDate, 
        enabledDate[Date] >= enabledDate[Enabled Date] && 
        enabledDate[Date] <= enabledDate[Enabled Date] + 7),[Count])),
        "ScreenCount", CALCULATE( MAX( enabledDate[Count] ) )
    ),
    "Screen",
    VAR CurrentCount = [ScreenCount]
    RETURN CALCULATE( MIN(enabledDate[Screen]), enabledDate[Count] = CurrentCount )
)

enabledDateSummary + 7 Output:

How to create summary tables based on EnabledDate, EnabledDate + 1, EnabledDate + 2, EnabledDate + 7 with date filter option on power bi?

1 Answers