Forum Discussion
Running Total (Cumulative)
Hello All,
I have created a measure as:
Running Sales =
CALCULATE(
SUM(FactTable[SalesAmt]),
FILTER(
ALLSELECTED(FactTable),
FactTable[SalesDate] <= MAX(FactTable[SalesDate])
It works fine, but I want to reset running Sales by Fiscal Year, which starts in October. I'm struglling with restting running total by Year. Here is the sample required output.
Thanks in advance.
3 Replies
Hello sabeensp
To create a YTD calculation I would recommend using a Date table and the DATESYTD time intelligence function:
- Create a 'Date' table with contiguous dates covering the required range
- Create a many-to-one relationship between FactTable[SalesDate] to 'Date'[Date]
- Create a measure like this:
Running Sales = CALCULATE ( SUM ( FactTable[SalesAmt] ), DATESYTD ( 'Date'[Date], "09/30" ) // Variants possible e.g. "2000-09-30" )
All date fields used in visuals should then come from the 'Date' table.
There are some tweaks you can make (for example hiding the measure when it goes beyond dates that exist in FactTable) but this is the starting point I would suggest.
I have attached a simple model illustrating this.
Regards,
Owen
OwenAugerThanks, I'll give it a shot. Here is the twist, what If I also want to reset running total by another attribute, such as by Gender. So Within Same FY also reset by Male and Female. Gender coming from teh sam efact table. Is it possible?
I'm new to DAX, so help is greatly appreciated.
Normally you would simply filter on other attributes such as Gender within your visual, and the running total would be calculated separately for each value of that attribute, since the running total modifies the date filter but leaves any other filters unchanged.
Could you give an example of how you would expect your visual/table to look if you included other attributes?
