Date Slicer: Automatic Min Date based on Max Date Selected

[PaulShlapa]

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

[Wilson_ · Accepted solution]

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?)

[PaulShlapa]

@Wilson_ , fantastic. Thanks, the trick did the job.

[Wilson_]

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?)

[PaulShlapa]

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_ · Accepted solution]

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?)

[PaulShlapa]

Hi Wilson,

You know i just checked the solution more precisely and found a problem.

I understood that the formula removes all filters from Sales_all_Years Table instead of the ones specified after komma. Still it looks like it just aggregates previous year data and applies to each Sales Company equally, bacause Sales Company filter is being removed. 

Here is what i mean (example PBIX):

Measure 1 = 
VAR _maxdate = MAXX(ALLSELECTED('Sales_all_Years'), 'Sales_all_Years'[Year])
RETURN
    CALCULATE (
        AVERAGE ( 'Sales_all_Years'[MM_by_Year] ),
        FILTER ( 'Sales_all_Years', 'Sales_all_Years'[Year] = _maxdate - 1 && Sales_all_Years[LP vs TP String Difference] = "Negative" ) )

Measure 2 = 
VAR _maxdate = MAXX(ALLSELECTED('Sales_all_Years'), 'Sales_all_Years'[Year])
RETURN
    CALCULATE (
        AVERAGE ( 'Sales_all_Years'[MM_by_Year] ),
        FILTER(ALL('Sales_all_Years'), 'Sales_all_Years'[Year] = _maxdate - 1 && Sales_all_Years[LP vs TP String Difference] = "Negative" ) )

Result:

Is there a way to also somehow tell measure 2 to control for Sales Company as well?

Link to PBIX File:

https://www.dropbox.com/scl/fi/youehk7n81965vsjgcqdo/YearRestrictingFilter.pbix?rlkey=8r0jioc75n79ii...

Sincerely,

Pavlo

[Wilson_ · Accepted solution]

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?)

[PaulShlapa]

Exactly what was needed, thanks again so much

[Wilson_]

Perfect, thanks for the update. Happy to help, Paul. 😄