Forum Discussion
Date Slicer: Automatic Min Date based on Max Date Selected
Dear Experts,
Is there a way to assign an automatic min date to a slicer ( minus 1 Year ) based on max date selected?
If i have a slicer that shows years, like the one below, i would like to automatically obtain 2022 if max date selected is 2023 (if max 2022 is selected than min should be 2021).
I need both dates to be in a slicer, because my measure refers to mindate and maxdate parameters.
The reason is that only comparisons between consequtive years are relevant and message or blank screen method is not preferrable once something else is chosen (creates too much misunderstanding).
Any ideas are very welcome.
Sincerely,
Pavlo
Hi Pavlo,
In your [Weighted Avg Revenue Negative previous] measure, wrap the 'Sales_all_Years' in your FILTER in your Total variance with an ALL and you should be able to go back to using single select in your year slicer. Without it, the FILTER still respects your original filter context and cannot, for example, pull 2022 if you have only 2023 selected in your slicer. Something like the below for your Total variance should work:
VAR total = SUMMARIZE( Sales_all_Years, Sales_all_Years[MM_by_Year], "Total", CALCULATE ( SUM ( Sales_all_Years[SalesRevenue_EUR_by_Year] ), FILTER ( ALL ( 'Sales_all_Years' ), 'Sales_all_Years'[Year] = _maxdate - 1 && Sales_all_Years[LP vs TP String Difference] = "Negative" && NOT ( ISBLANK ( 'Sales_all_Years'[MM_by_Year] ) ) ) ) )----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)Paul,
Thanks, very helpful to know what your data model looks like. My main point from the change I shared was that FILTER wouldn't work for you because it respects the original filter context, as mentioned. Lots of ways to update the measure itself, depending on your data model.
Here is one way to write Measure 2, based on your pbix:
Measure 2 = VAR _maxdate = MAXX(ALLSELECTED('Sales_all_Years'), 'Sales_all_Years'[Year]) RETURN CALCULATE ( AVERAGE ( 'Sales_all_Years'[MM_by_Year] ), 'Sales_all_Years'[Year] = _maxdate - 1, Sales_all_Years[LP vs TP String Difference] = "Negative" )This is what my report page looks like, after changing the year slicer to a single select and picking 2023. As you originally mentioned, Measure 1 returns nothing with a single year selected and using FILTER in your measure. Measure 2 keeps all your other filters and only replaces the Year column filter with the previous year.
However, I strongly suggest having a separate calendar dimension table if you don't already have one in your full model.
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)
8 Replies
Wilson_Memorable Member
Hi Paul,
Instead of automatically setting the min year in the slicer, you should be able to set the min date in your measure instead. 🙂
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)Hi Wilson,
That is actually a problem. I have two measures:
Weighted Avg Revenue Negative last = VAR _maxdate = MAXX(ALLSELECTED('Sales_all_Years'), 'Sales_all_Years'[Year]) VAR total = SUMMARIZE( Sales_all_Years, Sales_all_Years[MM_by_Year], "Total", CALCULATE( SUM(Sales_all_Years[SalesRevenue_EUR_by_Year]), FILTER('Sales_all_Years', 'Sales_all_Years'[Year] = _maxdate && Sales_all_Years[LP vs TP String Difference Lead] = "Negative" && NOT(ISBLANK('Sales_all_Years'[MM_by_Year]) ) ))) VAR weight_ = ADDCOLUMNS( total, "Weight", DIVIDE( [Total], SUMX(total, [Total]))) VAR weighted_avg = ADDCOLUMNS(weight_, "Weighted_Avg", [Weight] * Sales_all_Years[MM_by_Year]) RETURN SUMX(weighted_avg, [Weighted_Avg])and
Weighted Avg Revenue Negative previous = VAR _maxdate = MAXX(ALLSELECTED('Sales_all_Years'), 'Sales_all_Years'[Year]) VAR total = SUMMARIZE( Sales_all_Years, Sales_all_Years[MM_by_Year], "Total", CALCULATE( SUM(Sales_all_Years[SalesRevenue_EUR_by_Year]), FILTER('Sales_all_Years', 'Sales_all_Years'[Year] = _maxdate - 1 && Sales_all_Years[LP vs TP String Difference] = "Negative" && NOT(ISBLANK('Sales_all_Years'[MM_by_Year]) ) ))) VAR weight_ = ADDCOLUMNS( total, "Weight", DIVIDE( [Total], SUMX(total, [Total]))) VAR weighted_avg = ADDCOLUMNS(weight_, "Weighted_Avg", [Weight] * Sales_all_Years[MM_by_Year]) RETURN SUMX(weighted_avg, [Weighted_Avg])They are the same with only difference _maxdate is current year and _maxdate - 1 - previous year.
They work fine, but whenever i filter out only one year, the measure for previous year stops working
if two years are chosen, than there is no problem
How could one still get previous year measure to work by filtering only one year in Year Slicer?
Sincerely,
Pavlo
- Wilson_
Hi Pavlo,
In your [Weighted Avg Revenue Negative previous] measure, wrap the 'Sales_all_Years' in your FILTER in your Total variance with an ALL and you should be able to go back to using single select in your year slicer. Without it, the FILTER still respects your original filter context and cannot, for example, pull 2022 if you have only 2023 selected in your slicer. Something like the below for your Total variance should work:
VAR total = SUMMARIZE( Sales_all_Years, Sales_all_Years[MM_by_Year], "Total", CALCULATE ( SUM ( Sales_all_Years[SalesRevenue_EUR_by_Year] ), FILTER ( ALL ( 'Sales_all_Years' ), 'Sales_all_Years'[Year] = _maxdate - 1 && Sales_all_Years[LP vs TP String Difference] = "Negative" && NOT ( ISBLANK ( 'Sales_all_Years'[MM_by_Year] ) ) ) ) )----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)
Wilson_ , fantastic. Thanks, the trick did the job.