Cumulative Totals with FY Slicer

[Andrea_Jess]

Hi All, 

I'm wanting to calculate cumulative totals but i'm struggling to make it work with my fiscal period slicer. 

Here is the PBIX with the model and the slicer i want to use. 

Table structure:

Within my table, i have a fiscal year column, a fiscal month and a fiscal date. Each month, our budget forecasts can change so we capture the full year as at any given month (i.e. we can see historically how, for example, jun-21 looked like for the full year - this is captured through the Fiscal Month col). The fiscal date col basically is the individual months within that period and it shows the snapshot for that period. 

What i want to do: 

If a user selects FY21 August in the slicer, i want to show a YTD view of budget. It should sum up the values of fiscal month August for the dates: 

1/06/21

1/07/21

1/08/21

If they select for example, FY21 July on the slicer, it should sum up for Fiscal Month July-21, the fiscal dates: 

1/06/21

1/07/21

by default, a month will always be selected on the slicer so it should sum up the fiscal dates up till and including the chosen month. No future dates should show. So if i select FY21 Oct, i want to sum up from the start of the fiscal year which is Jun21 all the way to and including Oct21. 

Thanks in advance

[amitchandak]

@Andrea_Jess , time intelligence with date table should help

end date of 5/32 , means year start at 6/1

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"05/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"05/31"))

Also check

Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-C...

[Andrea_Jess]

Hi Amit, 

Not sure if that's exactly what i'm after but i could be wrong in my understanding. I  essentially need one measure because i want to visualise in a card visual. By default, slicer is single select so i want the measure to calculate ytd as of the month that is selected. 

The table structure is a bit odd due to the three financial colums. it's attached above for reference. 

Thanks for the links also. 

[amitchandak]

@Andrea_Jess , If you select a month(month year) from date table. these measures will be able to give you YTD. Based on Jun -2021 start date.

[Andrea_Jess]

Hi Amit, 

When i use 'YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"05/31"))', and i select a fiscal month it works but not in the way i want. 

For example, if i select August 2021, you can see that the slicer is selecting all dates within this period. However, what i want is for my card to only show the sum of values for July and August. I do not, however, want to alter the slicer.

The reason for this is if a user selects 'Augst 2021' on the slicer, it should show them a historical snapshot of what August looked like (full year). i will have other views that will give them a full year view so i can't change the slicer values.

Just for this YTD view, i want the output to look like this (as an example). I want it to be 10 but i want the DAX to somehow understand that depeneding on what fiscal month is seleted, only sum of the figures up till that month. 

if you look at the PBIX attached in the original question, you will see that my table has a fiscal month and then a fiscal date field. Each fiscal month will have 12 fiscal dates (dates for full year). 

Hopefully i haven't confused you. Thank you in advance, appreciate any help.

[v-yingjl · Accepted solution]

Hi @Andrea_Jess ,

Noticed that you have used both Fact table columns as the slicer, you can try:

YTD_2 = 
CALCULATE (
    SUM ( 'Master_Data'[Spend] ),
    FILTER (
        'Master_Data',
        'Master_Data'[Financial Date]
            <= SELECTEDVALUE ( 'Master_Data'[Financial Month] )
            && 'Master_Data'[Financial Date]
                >= EOMONTH ( SELECTEDVALUE ( 'Master_Data'[Financial Month] ), -2 ) + 1
            && 'Master_Data'[Costing Element] = "Budget"
    )
)

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Andrea_Jess]

Thank you so much!

[Andrea_Jess]

Hi Amit,

In my model, i'm linking my date table to my 'Financial Date' field in my Fact table. 

When i use the DAX formula above, i get BLANK() when i select a value on my slicer. Need to bring in Fiscal Year and Fiscal Month columns from my date dimension table as users need to slice by fiscal period. 😞