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!