Filtering based on a selected Date slicer value

[learning_dax]

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.

[jgeddes · Accepted solution]

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]

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?

[learning_dax]

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