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 ) )
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 work
Margin 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.
Hi tamerj1
Thanks for your reply!
In below screenshot you can see that Jan 31 (week start date) is mapped to both Jan and Feb and same for Feb 28 which is mapped to Feb and March.
This is because Jan 31 which is week start date includes Feb month dates. Is there any possibilty that Jan 31 entire week should be entirely mapped to Jan month and same for Feb 28 and for every month end.
Also, your Cumulative measure does not give correct result it looks it is doing cumulative sum weekly basis but we need cumulative sum on month basis.
If we change VALUES('Date'[Week Rank]') to VALUES('Date'[Month Year]) that would work.
Thanks!
- tamerj14 years ago
Community Champion
apatwal
Regarding your first concern. Yes this is reasonable because the week that starts on Jan. 31 ends on Feb. 06 and the week that starts on Feb. 28 ends on Mar. 06 therefore both weeks exists in two months. This is also related to the 2nd concern. You are trying to slice by weeks and month at the same time, which is not an easy task. Handling weeks requires extra attention and different approach.
Now allow me to go back again to your 1st concern. If you remove the Month Year column from the table visual the problem will be solved.
If you are ok with the then I can update the code so it can decide automatically either to iterate over weeks of over months. Thank you- tamerj14 years ago
Community Champion
apatwal
This is the updated sample file https://www.dropbox.com/t/zS12j58c8c1BlRNY
The final measure areMargin 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'[Month Year] ), CALCULATE ( IF ( NOT ISBLANK ( [Difference in Margin] ), CALCULATE ( [Difference in Margin], REMOVEFILTERS ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) ) ) ) )Thank you!
- apatwal4 years ago
Helper III
Hi tamerj1
Thanks for your reply!
I changed my measures as per your guidance but if you see below screenshot, my cumulatives values are taking duplicate values at the month end
It should be as below:
Jan 2022 - 1307920
Feb 2022 - 2297153 (but in above ss, it is +84645 From Jan 31)
Mar 2022 - 2407155 (from above ss, it is +84645 From Jan 31 and +118894 from Feb 28)
This is the main issue that needs to be resolved.