Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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