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]))
- apatwal4 years ago
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!- 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...