Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
zeroblack7
Regular Visitor

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 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
Table Calcurate Correct.JPG

 

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]))
))
 
 Fail DAx.JPG

 

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

1 ACCEPTED SOLUTION
AntrikshSharma
Super User
Super User

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

1.png

 

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 )

 

 

View solution in original post

7 REPLIES 7
AntrikshSharma
Super User
Super User

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

1.png

 

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 )

 

 

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

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

littlemojopuppy
Community Champion
Community Champion

Hi @zeroblack7.  Something like this???

littlemojopuppy_0-1609168943287.png

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!

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

zeroblack7_0-1609217210030.png

 

What I want is something looklike this 

 

zeroblack7_1-1609217328670.png

 

amitchandak
Super User
Super User

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

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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.

zeroblack7_2-1609218214003.png

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.