Forum Discussion
Help in Cumulative Total
Hi
I am facing issues with Cumulative Total. Below are the DAX I am using for cumulative total.
I have below table
Week Start Date | Margin | Margin Last Year Week | Difference in Margin
I have to take Cumulative of Difference in Margin
Margin is measure
Margin Last Year Week is measure calculated as
We have neagative values from March as we don't have data from March 2022 (difference is calculated by taking difference of previous year week from current year week in focus.)
so Feb cumulative value should be 4997451 + 2398854 = 7396305 ( which is coming as 7494045 which is wrong )
I want to display this Cumulative value in column chart and also I want that it should display till Feb without using any invoice filter as we have data from Jan 2021 to Feb 2022 and data is populated on weekly basis so this graph will gradually increases weekly.
Any help will be appreciated.
Thanks!
Hi apatwal
Here is the updated file https://www.dropbox.com/t/l8yJiCeJNMa9ItgD
Again you are right. And again I shifted all calculations down to day level in order to obtain correct results dynamically regardless on which level slicing is. Changes are mainly in the last year margin measure. However following you can find the code of all measures and calculated columns
Day Rank column in the date table:Day Rank = RANKX ( 'Date', 'Date'[Date],, ASC )Then the measures are
Total Margin = SUM ( 'Margin data'[Margin] )ShowValueForDates = VAR LastDateWithData = CALCULATE ( MAX ( 'Margin data'[Invoice Date] ), REMOVEFILTERS () ) VAR FirstDateVisible = MIN ( 'Date'[Date] ) VAR Result = FirstDateVisible <= LastDateWithData RETURN ResultMargin Same Period Last Year = IF ( [ShowValueForDates], VAR FirstDayRanknPeriod = CALCULATE ( MIN ( 'Date'[Day Rank] ), 'Date'[DateWithData] = TRUE () ) VAR LastDayRankInPeriod = CALCULATE ( MAX ( 'Date'[Day Rank] ), 'Date'[DateWithData] = TRUE () ) RETURN CALCULATE ( [Total Margin], REMOVEFILTERS ( 'Date' ), 'Date'[Day Rank] >= FirstDayRanknPeriod - 364, 'Date'[Day Rank] <= LastDayRankInPeriod - 364 ) )Difference in Margin = VAR CurrentYear = MAX ( 'Date'[Year] ) VAR CurrentYearMargin = CALCULATE ( [Total Margin], 'Date'[Year] = CurrentYear ) VAR MarginLastYear = [Margin Same Period Last Year] RETURN IF ( NOT ISBLANK ( MarginLastYear ), CurrentYearMargin - MarginLastYearMargin Diff Cumulative = VAR LastDayInFilter = MAX ( 'Date'[Day Rank] ) RETURN IF ( [ShowValueForDates], CALCULATE ( [Difference in Margin], REMOVEFILTERS ( 'Date' ), 'Date'[Day Rank] <= LastDayInFilter ) )
24 Replies
- Whitewater100
Solution Sage
Hi:
I agree with tamerj on using Yr-Week No.
To get your graph to display correctly you can do your measure like this:
Measure = IF( ISBLANK([Margin], BLANK(), [Incremental Margin Difference]))
- apatwal
Helper III
Hi tamerj1
I had tried using Month Name which is correct showig only filtered data but my cumulative totals are coming wrong.
I am calculating margin difference week wise and then taking cumulative month wise.
Can you be more specific where to use Year-Week number; I am confused here.
I have given below DAX to calculate Week Rank
Week Rank =RANKX(ALL('Date'),'Date'[Week Start Date],,ASC,DENSE)Appreciate your response!
- tamerj1
Community Champion
Hi apatwal
Please refer to the sample file with the solution https://www.dropbox.com/t/D5m4mMmNS3CVhZA1
The measures areDifference in Margin = SUMX ( VALUES ( 'Date'[Month Year] ), CALCULATE ( IF ( NOT ISBLANK ( SUM ( Margin[Margin] ) ), CALCULATE ( SUM ( [Margin] ) - [Margin Last Year Week], 'Date'[Year] = 2022 ) ) ) )Margin Diff Cumulative = IF ( NOT ISBLANK ( [Difference in Margin] ), CALCULATE ( [Difference in Margin], REMOVEFILTERS ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) ) )- apatwal
Helper III
Hi tamerj1
This works perfectly fine for me! Thanks for your help.
But there is some changes which are needed.
If you see in below screenshot, to calculate difference in margin, the last value of Feb 28 is being considered to calculate difference rather than addition of all values.
It should be 195325 - 195462 (total of all Margin Last Year Week for a month)
Also, if we do not have Margin values then it should not be considered while calculating difference. Like in below, Difference for March month should be 6995.
below DAX is being used to calculate Margin Last year week.
Margin Last Year Week =CALCULATE([Margin],FILTER(ALL('Date Table'),'Date Table'[Week Rank] = MAX('Date Table'[Week Rank])-52))- tamerj1
Community Champion
apatwal
No it is not working perfectly! Actually all the numbers were wrong as I did not pay attention the first measure [Margin Last Year Week].
The following should workMargin Last Year Week = SUMX ( VALUES ('Date'[Week Rank] ), CALCULATE ( IF ( NOT ISBLANK ( SUM ( Margin[Margin] ) ), CALCULATE ( SUM ( Margin[Margin] ), REMOVEFILTERS ( 'Date' ), 'Date'[Week Rank] = MAX ( 'Date'[Week Rank] ) - 52 ) ) ) )Difference in Margin = SUMX ( VALUES ( 'Date'[Week Rank] ), CALCULATE ( IF ( NOT ISBLANK ( SUM ( Margin[Margin] ) ), CALCULATE ( SUM ( [Margin] ) - [Margin Last Year Week], 'Date'[Year] = 2022 ) ) ) )Margin Diff Cumulative = SUMX ( VALUES ('Date'[Week Rank] ), CALCULATE ( IF ( NOT ISBLANK ( [Difference in Margin] ), CALCULATE ( [Difference in Margin], REMOVEFILTERS ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) ) ) ) )Still not sure about the blanks issue as my sample data don't have blanks. So please check and let me know. Thank you.
- AnonymousNot applicable
Hi apatwal ,
Is your problem solved, if not, Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.
For problems with totals in your question, you can create a measure.
Sum_Total = var _table=SUMMARIZE('Date', 'Date' [Month Year],"_value",[Margin Difference Cumlative]) return IF(HASONEVALUE('Date' [Month Year]),[ Margin Difference Cumlative],SUMX( _table,[_value]))Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
- apatwal
Helper III
Hi Anonymous
Please find the attached PBI Sample file
I had calculated weekly difference of Margin and want cumulative sum Month wise.
Let me know if you need any futher information.