Filter a report to limit to X number of months based on what is the max month in the data

[AdsChan]

Hi all,

Although I'm fairly new to Power BI, I've been really trying to figure this one out with not much luck.

In Australia, the financial year is from July to June. 

So July 2020 to June 2021 was Financial Year 2021

Then July 2021 to June 2022 was Financial Year 2022

And so on...

In my report, I want to always show a maximum of 3 Financial Years - the earliest 2 financial years should always be complete years, but the 3rd year doesn't need to be completed yet, it can show the year to date depending on what month we are in. So for example, if my Max report month was June 2023, then I will have 3 completed financial years and the maxium number of months I want showing for the report (ie; 36 months).

But if now the latest report month is July 2023, this means that we are in a new Financial Year of 2024. So then what I would like showing in the report is Financial Year 2022, Financial Year 2023 and the Financial Year to date of 2024. Basically the dates from July 2021 to July 2023 or the last 25 months. Then when the max month is Sep 2023, it should show from July 2021 to Sep 2023 or the last 26 months - and then so on until we get to June 2024 where it will be a max of the last 36 months. 

Then in July 2024 when we start a new financial year again, we then want the last 25 months again...

I hope that made sense. 

My initial thoughts around this was maybe use something like the MONTH function in Excel where it returns a number from 1 to 12. So then the logic would be if Month = 8 (as in Aug), then filter for 26 months. If Month = 1, then filter for 31 months. And so on until Month = 6 for 36 months. I dont quite understand how to do this in Power BI, but I'm hoping anyone can suggest a solution? Hopefully an even better one that what I am thinking.

Many thanks

[AmiraBedh]

[AmiraBedh]

I think it is related to the privilege you have on the platform.

Try to put your pbix file in a Google Drive folder for example and provide the link.

[AdsChan]

I dont seem to have that option?

[Ashish_Mathur]

Hi,

Please find attached a PBI file with a similar solution.  You will notice that the dates on the X-axis will how the las 6 days based on the Date selected in the slicer.

[AdsChan]

Thank you for the example. I dont think this will work with what I was trying to achieve for 2 reasons

1. this is based on a selection made with the slicer - but how would it work if i wanted it to be against the Max Month automatically for the entire page/report? As in there is no manual selection on a slicer or filter, but just automatically calculating against the Max Month?

2. The min selection there is always -6 days from date selected. But the Min selection I would need will need to by dynamic depending on what the Max month is. So if its August, then I need to retun the last 25 months. If its December, I need it to return the last 30 months. If its June, then return the last 36 months - and that would be the most. Once it is July, it is back to returning the last 25 months 

[Ashish_Mathur]

Hi,

Share some data to work with.

[AdsChan]

Hi, I made a pbix file as an example with some data, but terribly embarrased to say that I cant see how I can upload it in this reply? So i just uploaded some screenshots.

[Ashish_Mathur]

Upload the file to Google Drive and share the download link here.

[AdsChan]

Thank you for the reply. 

When you say create a Date table that covers the range of my data - what if the date range changes every month? For example, the query I have is returning 36 months from Max Month Date. But the issue I have is that I dont always wants it to show 36 months in the report. If its July, I only want to show 25 months. If its Aug, I want it to show 26 months. And then so until its Jun where I want it to return 36 months.

So then if I create a Date table, Are you saying I should just put future dates going ahead several years?

Also when you say - "the Financial Year End Date I am supposing the end date will be the max date in your data." - no it is not. This is why I mention that it depends on the month, the 3rd financial year could just be the financial year to date and not a completed financial year yet. So if the Max Month is August, then I am only 2 months into the current Financial Year, as the year starts in July.

Hope that makes sense. Thanks

[AmiraBedh]

You'll be needing to construct a dynamic date filter that looks at the current month in your dataset and then determines the range of dates you need to include in the report.

Create a Date Table if you don't already have one, you should create a date table that contains a list of dates that covers the range of your data.

Then create a measure to calculate the max date from your dataset :

MaxDate = MAX('YourDataTable'[Date])

You will want to calculate the start date of the financial year range you want to report on. This will depend on the max date in your data :

StartDate =
IF(
MONTH([MaxDate]) >= 7,
DATE(YEAR([MaxDate]) - 2, 7, 1),
DATE(YEAR([MaxDate]) - 3, 7, 1)
)

Calculate the Financial Year End Date I am supposing the end date will be the max date in your data.

Finally, you can use the start date and end date in the filters section of your report to limit the data to the range you're interested in.

The `StartDate` logic works by looking at the month of the max date in your data. If the month is July or later, it subtracts 2 from the year to find the starting point for your financial year range. If it's earlier than July, it subtracts