YTD/MTD/QTD with ‘FY slicer’ - PY slicing issue

DAX DAX Calculations
1

Hi fellow Power BI-users,

I am having issues with time intelligence functions in Previous (fiscal) years while using a Fiscal Year-slicer.

I would like my YTD/PYTD, MTD/PY MTD and QTD/PY QTD to always reference back to the latest date from my Fact table (P&L data set).

E.g., today we are April 2020 and my Fact table has data up to February 2020. My fiscal years run from July 1st-June 30th.

In each sliced year, it should only take totals up to February month.

Currently, when I select a previous FY (older than current FY) in my slicer, the measures are showing values up to December month from previous FY, while I would like them to show totals up to only February 2019, the max date from my P&L fact table.

I assume this has to do with the fact that currently the formules look at the MAX date from the Fact table.

If the FY slicer is set to current FY, it ofcourse will take only to February 2020, because there’s no data any further. However, all previous FY’s have data for all 12 months from each year.

What it should show when slicing on my previous FY:

YTD: July 2018-February 2019
PYTD: July 2017-February 2018
MTD: February 2019
PYMTD: February 2018
QTD: January 2019-February 2019
PYQTD: January 2018-February 2018

-What do you think would be the best approach to facilitate the above?
-What formulas should I use for the measures?
-Should I use Time intelligence-formulas or use my calculated ‘P&L MaxMonthOffset’-column in my Date table?

It might be helpful if there’s someone with a PBI-file that contains the same table structure with working measures.

FYI: I use a Date table with dates up to June 30th next year. It includes dates further than the max. date from my P&L Fact table, because I will be adding Budget/Forecast data from other Fact tables as well soon.

The Dates table includes various columns including Fiscal Year which I use for the slicer. It also includes a calculated column with a working MonthOffset from the P&L Fact table. So for February 2020, my MonthOffset column shows 0. For past months it counts down & for future months it counts up.

Thank you very much for your feedback!

Kind regards,
Bram

2

Hi @dillenbram ,

See how you get on with this. I’ve added a Calculated Column to the Dates table to identify the Last Sales date in the Previous Year, with that in place it can used as an additional filter.

Sales FYTD = 
TOTALYTD( [Total Sales],
    Dates[Date], ALL( Dates[Date] ), "30-6" 
)

and for the previous year.

Sales PY FYTD = 
CALCULATE( [Sales FYTD],
    SAMEPERIODLASTYEAR( Dates[Date] ),
    Dates[IsPast] = TRUE()
)

Calculations for TOTALQTY and TOTALMTD basically follow the same pattern.

Here is my sample file. eDNA - FY Sales YTD and PYTD .pbix (462.7 KB)
I hope this is helpful

3

Hi @dillenbram, we’ve noticed that no response has been received from you since the 25th of April. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

4

Thank you, @Melissa !

There’s one remaining issue with these formulas when comparing multiple years: It doesn’t deal with Leap Year values correctly.

If I compare YTD February 2021 with February 2020, the PYTD value does not include February 29th 2020 (the additional Leap year-day from that year).

How could we solve this?

Thank you for your help!

Kind regards,

Bram

5

Hi Bram,

See if this article will help you to resolve that remaining issue.