DAX: Cumulative Comparison (Count row from table visual)

Hi, Community. This is my first time post on community. Please advice if i'm doing something wrong. I need some help for DAX to calculation on my report it a little bit complicate but I'll t...

DAX

AntrikshSharma

5 years ago

zeroblack7 File is attached below my signature, I have also included a custom Date table.

GrowthStatusCount = 
VAR CurrentDate =
    MAX ( Dates[Date] )
VAR LastMonday =
    CurrentDate - WEEKDAY ( CurrentDate, 3 )
VAR Result =
    FILTER (
        ADDCOLUMNS (
            CALCULATETABLE (
                SUMMARIZE ( fSalesTable, Dates[Date], Dates[Day Name], dCompanyGroup[Company] ),
                Dates[Date] = LastMonday
            ),
            "@Growth Status", [GrowthStatus]
        ),
        NOT ISBLANK ( [@Growth Status] )
    )
RETURN
    COUNTROWS ( Result )

Sample Data Power BI.pbix142 KB

littlemojopuppy

Community Champion

5 years ago

Hi zeroblack7. Something like this???

You can download the modified pbix here.

Modified the date table to include year and week number (along with a couple other things). From there the logic is essentially find the most recently completed week and then determine sales for it and the previously completed week. Subtract and calculate % change. Measures are below.

Total Sales Previously Completed Week = 
    VAR CalendarSummary =
        GROUPBY(
            FILTER(
                ALL(dDate),
                dDate[Date] <= SELECTEDVALUE(dDate[Date])
            ),
            dDate[Year],
            dDate[WeekNumber],
            "DayCount",
            COUNTX(
                CURRENTGROUP(),
                dDate[Date]
            ),
            "WeekEndingDate",
            MAXX(
                CURRENTGROUP(),
                dDate[Date]
            )
        )
    VAR PreviousWeekEnding =
        MAXX(
            FILTER(
                CalendarSummary,
                [DayCount] = 7
            ),
            [WeekEndingDate]
        )
    RETURN

    CALCULATE(
        [TotalSalesAmount],
        DATESINPERIOD(
            dDate[Date],
            PreviousWeekEnding - 7,
            -7,
            DAY
        )
    )

Total Sales Most Recent Completed Week = 
    VAR CalendarSummary =
        GROUPBY(
            FILTER(
                ALL(dDate),
                dDate[Date] <= SELECTEDVALUE(dDate[Date])
            ),
            dDate[Year],
            dDate[WeekNumber],
            "DayCount",
            COUNTX(
                CURRENTGROUP(),
                dDate[Date]
            ),
            "WeekEndingDate",
            MAXX(
                CURRENTGROUP(),
                dDate[Date]
            )
        )
    VAR PreviousWeekEnding =
        MAXX(
            FILTER(
                CalendarSummary,
                [DayCount] = 7
            ),
            [WeekEndingDate]
        )
    RETURN

    CALCULATE(
        [TotalSalesAmount],
        DATESINPERIOD(
            dDate[Date],
            PreviousWeekEnding,
            -7,
            DAY
        )
    )

Weekly Variance = [Total Sales Most Recent Completed Week] - [Total Sales Previously Completed Week]

Weekly % Variance = 
    DIVIDE(
        [Weekly Variance],
        [Total Sales Previously Completed Week],
        BLANK()
    )

Hope this helps!