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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.