Forum Discussion
Cumulative Total using DAX for FY not CY
Hi Community,
TLDR: Need help showing cumulative sum of sales from November, not January, as it comes from stock DAX.
I have a very simple issue, that I have tried googling about but couldn't find any solution that fit to my data samples.
I have used the out-of-box Running Total DAX from PD Desktop and I'm very happy with the results as shown below:
Now client has a requirement that instead of basing my cumulative total on a CY (Jan-Dec) basis, we should do it on a FY basis (Nov-Oct).
Here is the DAX (autogenerated) that PB Desktop had for me:
Test =
CALCULATE(
[Sales], //Sales = SUM(BudgetItemTest[Sales])
FILTER(
CALCULATETABLE(
SUMMARIZE(
'BudgetItemTest',
'BudgetItemTest'[Date].[MonthNo],
'BudgetItemTest'[Date].[Month]
),
ALLSELECTED('BudgetItemTest')
),
ISONORAFTER(
'BudgetItemTest'[Date].[MonthNo],MAX(BudgetItemTest[Date].[MonthNo]), DESC,
'BudgetItemTest'[Date].[Month],MAX(BudgetItemTest[Date].[Month]), DESC
)
)
)
I don't have a CalendarTable (ex:DimDate) but I do have a date field in my table as shown in code and hope to get a solution without creating a 'DimDate' table. Now, I tried tweaking it and doing multiple stuff (involving googling various keywords related to this) but I cannot figure out how to make running totals start from November instead of January.
Can anyone, please, explain to me, how to do so?
P.S. If anyone needs, I can share a different .pbix with random data with same structure as client's, but hopefully it isn't required.
Thank You.
2 Replies
- Greg_Deckler
Community Champion
Anonymous You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008 - AnonymousNot applicable
Hi Anonymous ,
According to my understanding, you want to sum values from November(Last year) to October(This year), right?
You could use RANKX() to sort Date column and then use SUMX() function to calculate the sum like this:
Rank = RANKX ( FILTER ( ALL ( 'Table' ), 'Table'[Date].[MonthNo] = 10 ), [Date], , ASC, DENSE )Sum = SUMX ( FILTER ( 'Table', 'Table'[Rank] = EARLIER ( 'Table'[Rank] ) ), [Value] )The final output is shown below:
Please take a look at the pbix file here.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.