Use Parameter to display last n days/weeks

[cbschley]

Hello all! I'm trying to build a DAX formula that filters a visual to only allow the last n days/weeks into the visual. The way I was going about this was to:

1. create a parameter from 1 to 20

2. build a DAX formula that checks if the dates in the table are >= the number of days before today's date

3. flag for 1 if it is, flag for 0 if it is not

4. filter for 1 on page-filters

The problem I seem to be running into is that when I reference the parameter in a DAX query, it only returns the default parameter value, and not the value that I specify. I assume it has something to do with being unable to selecting a single value from an unlinked table, but not entirely sure.

Below is a visual that helps describe the issue:

todays date: 1/13/2023

week start date: this is source data that I want to compare against todays_date
weeks_param: this is the parameter value I set in the visual
week_check: this is my logic check, where I'm checking to see what date it returns when you take todays_date - weeks_param. As you can see, its defaulting to 0, hence why it returns 1/13/2023

What am I doing wrong? This is how I would tackle this problem in Tableau, so maybe i'm thinking about the solution wrong.

[johnt75 · Accepted solution]

You're doing it in a calculated column and that won't work. Calculated columns and tables are only calculated during data refresh, so they don't pay attention to slicer values, which is why you're seeing the default value everywhere.

You need to do the same thing but in a measure so that it is calculated dynamically and will respond to the slicer.

Create a measure like

Week check =
VAR ReferenceDate =
    SELECTEDVALUE ( 'Table'[week start] )
VAR NumDays =
    SELECTEDVALUE ( 'Slicer value'[Slicer value] )
RETURN
    IF ( ( TODAY () - NumDays ) <= ReferenceDate, 1 )

and add that as a visual level filter to your table, to only show when the value is 1

[DanielCarvalho]

Hello, I was trying to adapt this to something similar...

I want to create a slicer where the user can input a number identifying how many days they want to filter the data by. So if they input 5, the visuals will filter data only from the 23/06/23 up to today(28/06/23). 

My table has a column called Date which updates everyday with the same rows as the day before but with possible new status for each ticket(row) and I can also create a column thats shows todays date if necessary. I created a numeric field paramter just as done above.

[johnt75]

I think I'd go about it in a different way. I'd create a calculation group with 1 calculation item like

Calc Item =
CALCULATE (
    SELECTEDMEASURE (),
    DATESBETWEEN (
        'Date'[Date],
        TODAY () - SELECTEDVALUE ( 'Num days'[Num days] ),
        TODAY ()
    )
)

[DanielCarvalho]

Trying to apply this but I am a bit confused on how to do so?

Is there a specific way to created a calculation group? And how do I apply it to my needs?

[johnt75]

You can't, yet, create calculation groups in Power BI Desktop, you need to use Tabular Editor. There's plenty of tutorials on how to do that.

Once you have the calculation group created you can apply it as a filter to the visuals where you want it to take effect, and it will work for any measures within those visuals.

[DanielCarvalho]

Thanks for your help. It is somewhat working, not perfect but that is more down to the way I have to present my data and not the solution itself. 

[cbschley]

Thank you @johnt75 ! Worked like a charm. I appreciate your help!