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 tamerj1
Thanks for your help!
I just have a small doubt here that we are not matching with Margin Last Year Week Data. Could you please help me to understand this. If I see the data for week start date 1/11/2021 on date wise we have data which sums up 65373 and this same value should be shown corresponding to 1/10/2022 week start date
Can you have a look into this..
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
)
)