Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User
Super User

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
Super User
Super User

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
Super User
Super User

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.