Forum Discussion
Help in Cumulative Total
- 4 years ago
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 ) )
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]))
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
- tamerj14 years ago
Community Champion
Hi apatwal
Your measure isMargin Last Year Week = CALCULATE ( [Margin], FILTER ( ALL ( 'Date' ), 'Date'[Week Rank] = MAX ( 'Date'[Week Rank] ) - 52 ) )What is the Week Rank column? Is it the week number from 1 - 52 or a unique number that do not recreate for other years
- apatwal4 years ago
Helper III
Hi tamerj1
Week Rank Column is in Date Table that is a unique number that do not recreate for other years. Below is the DAX used to create Week Rank Column
Week Rank =RANKX(ALL('Date Table'),'Date Table'[Week Start Date],,ASC,DENSE)For Jan 2021For Jan 2022
Let me know if I am doing something wrong here...
As my cumulative values are coming wrong...
- tamerj14 years ago
Community Champion
Nothing wrong with that. To fix the total issue:
step 1 replace MAX with MIN and spot any difference in the montly values. If so then you have issues with your data that need to be fixed before moving forward. If nothing change at month level the move to step 2
step2: create a new measure
New Measure =
SUMX (VALUES ( Date[Year Month] ),
[Old Measure]
]
step3: use the new measure in your visual instead of the old one.