cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
learning_dax
Helper II
Helper II

Filtering based on a selected Date slicer value

Hi all,

 

Been noodling on this for some time now. I have a dashboard that gives monthly snapshot of sales, # of interviews, etc. The entire page revolves around a Month & Year slicer, so that I can send this report out monthly and the business has total monthly sales, last month sales, etc. However, I am stuck on the future sales calculation. I am trying to build a measure that shows future sales but based on the selected value of the Month & Year slicer. 

For example, if I send out the September 2022 report, since the fiscal year of the business ends May 31 and begins June 1, the calculation will show all months after September (for future sales) but will stop on May 31 of 2023. Likewise, for Octobers report (the slicer will show Month & Year: October 2022) it will show future sales afterthe month of October but only up until the fiscal year end date which is May 31, 2023. 

I've been able to build this monthly sales report & its "last month" calculations by using Month offset = -1 while Month & Year is selected to a certain month, but I know the future sales part is a bit more nuanced. Any tips? Thanks everyone.

1 ACCEPTED SOLUTION
jgeddes
Solution Sage
Solution Sage

Sorry I misread your initial post. If you are looking for current future sales confined to the current fiscal year you could add a isCurrentFiscalYear column that returns Yes or No if a date is in the current fiscal year to your date table and then use that column as a limiting filter in your future sales calculation.

View solution in original post

3 REPLIES 3
jgeddes
Solution Sage
Solution Sage

Sorry I misread your initial post. If you are looking for current future sales confined to the current fiscal year you could add a isCurrentFiscalYear column that returns Yes or No if a date is in the current fiscal year to your date table and then use that column as a limiting filter in your future sales calculation.

jgeddes
Solution Sage
Solution Sage

I am not sure there is enough information here to give a concrete answer however in my experience I have run into issues where I had a calculated max date on my date table that was limiting my measures simply because it had no more dates past a date. In this case I am wondering if your date table has dates past May 31, 2023?

Yes my calendar table has dates past May 31, 2023. I simply only want "future sales" based on a given slicer Month/Year value but only for that current fiscal year. I don't want future sales for the entirety of Order Dates but only for this fiscal year, which is why I want > the current Month/Year selected but limited to the end of the fiscal year which is May 31, 2023

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.