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 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
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.