DAX: Cumulative Comparison (Count row from table visual)

[zeroblack7]

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 try to explain as much as I can.

Here is my Sample File (pbix): https://bit.ly/2KXqBz5

(xlsx for raw data): https://bit.ly/3mSYj66

My Ultimate Goal is to create "Card visual that told me the number of my major client that sales is spike in previos week". (To use for notification alert)

I would make this alert every Monday, So the calculation would Sum of Last Monday till Last Sunday VS Sum of Last Monday till Last Sunday But Week 2 Week Ago.

(Example: If today is Monday , September 28, 2020

Last 7 Day will be Monday , September 21, 2020 till Sunday , September 27 , 2020

Last 7 Day 2 Week Ago will be Monday , September 14, 2020 till Sunday , September 20 , 2020)

And minus between 2 of those and make it % .

Lastly check 2 condition to show the status. (1st last7 day cummulative must more than 1million, 2nd Difference Cummulative from Last 7 Day and Last 7 Day Ago % must more than 10% or lower than -10%.

*Sorry for my poor english

My current progress:

DAX I created

//Basic Sum

TotalSalesAmount = SUM(fSalesTable[Amount]) 

//Cummulative Last 7 Day

Cummu L7D =

VAR Last_Date = Max(fSalesTable[Date])

RETURN

CALCULATE( [TotalSalesAmount],

FILTER( ALL( dDate),

dDate[Date] > Last_Date - 7 &&

dDate[Date] <= Last_Date ) )

//Cummu L7D2WAgo

Cummu 7D2WAgo =

CALCULATE(

[Cummu L7D] ,

DATEADD(dDate[Date] , -7 , DAY )

)

//Lastly Algorithm check criteria.

GrowthStatus = SWITCH( TRUE() ,

[Cummu L7D] > 1000000 && [DiffCummu WoW %] >= 0.10 , "Positive" ,

[Cummu L7D] > 1000000 && [DiffCummu WoW %] <= -0.10 , "Negative" ,

BLANK()

)

When I put all my measure to Visual Table it look calcurate correctly

But when try to created DAX that told me the Number of client that spike in last Monday it seem not work correctly here is my current DAX (I tried variation of SUMMERIZE and GROUP BY but still not working for me)

Count Alert All =

VAR LastMondayValue = CALCULATE (MAX(fSalesTable[Date]) , dDate[Date] < TODAY() , WEEKDAY(dDate[Date]) = 2 )

RETURN

COUNTROWS(

FILTER(

FILTER(

SUMMARIZE(

fSalesTable ,

dCompanyGroup[GrouppingCompany],

fSalesTable[Date],

"GrowthStatus" , [GrowthStatus]

),

fSalesTable[Date] = LastMondayValue

),

NOT(ISBLANK([GrowthStatus]))

))

 

The Card should provide me number that count of GrowthStatus column.

Any idea or advice for me to using properly DAX.

Thank you your all in advance.

Jay

[AntrikshSharma · Accepted solution]

@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 )

[zeroblack7]

Thank you @AntrikshSharma  This is what i'm looking for.  Your Dax is quite complex to me but I will learn a lot from your . Thanks again.

Jay

[AntrikshSharma]

@zeroblack7  You're welcome! Thanks for creating a detailed post and sharing what you have tried so far. Everything was easy to understand.

[littlemojopuppy]

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!

[zeroblack7]

Hi @littlemojopuppy  Thank you for your reply but this is not what I'm looking for. 

What I want is something looklike this 

[amitchandak]

@zeroblack7 , For WOW , please refer to my blogs

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

or video : https://www.youtube.com/watch?v=pnAesWxYgJ8

You need to create few columns in the date table :

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

And then you can have measures like

This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
Last 8 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
last two weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]<=max('Date'[Week Rank])-1
&& 'Date'[Week Rank]>=max('Date'[Week Rank])-3))

[zeroblack7]

Hi, @amitchandak Thank you for your reply. Now I'm watching you Video. But I'm looking for something that can count my table after put some measure in there.