Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Andrea_Jess
Helper III
Helper III

Cumulative Totals with FY Slicer

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

 

 

 

1 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"
    )
)

vyingjl_0-1636102665864.png

 

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.

View solution in original post

7 REPLIES 7
amitchandak
Super User
Super User

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

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. 

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

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.

Andrea_Jess_0-1635929512046.png

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.

Andrea_Jess_1-1635929696573.png

 

 

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"
    )
)

vyingjl_0-1636102665864.png

 

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.

Thank you so much!

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.