Display last month values in date slicer

Hi,

I am strugging to find out a solution to always display values of last 7 days in visual date slicer. User don’t want to use Relatives Date.

I have managed to create a new column on my date calendar which gives a value of 1 one when evaluates to true if date is within -7/7 days of max dates in the Fact Table:

Last 7 days =
VAR _maxdate = MAX ( ‘ FactTable ’[Date] ) 
RETURN IF (
    DATEDIFF ( Calendar[Date]; _maxdate; DAY ) IN GENERATESERIES ( -7; 7; 1 );
    1;
    0
)

I apply this measures to filter on visual and Advanced Filtering by Values = 1.

The problem is that when refreshing data, slicer’s values won’t change and update.

Any ideas how may I improve this behaviour?

Thanks in advanced!

Have you examined the Dynamic Date Range Slicer, in the M code Showcase category?

.
You can add and or modify Periods to your needs. If you need any further assistance please reach out. Thanks.

I hope this is helpful.

1 Like

Hi @judy_fmf

Create a calculated column on Calender table with below logic

Last 7 Days =
VAR _maxdate = MAX( ‘ FactTable ’[Date] )
RETURN
IF ( DATEDIFF(Calendar[Date], _maxdate,DAY ) >=0 && DATEDIFF(Calendar[Date], _maxdate,DAY ) < 7, “Last 7 Days” )

Apply filter on Slicer using this Calculated column( Select value “Last 7 Days”)

1 Like

@judy_fmf,

Here is the M Code if you want the last 7 Days, not including today:
Last 7 Days Not Including Today.txt (2.9 KB)
image

Here is M code if you want to include today:
Last 7 Days Including Today.txt (2.8 KB)
image

Thanks
Jarrett

2 Likes

Great @JarrettM and @Melissa ! This is amazing work. This is just what I need it.

I have also tried @Rajesh proposal and works smoothly.

I need to check which is better for my model and let you know the results.

Thanks again :wink:

1 Like

@judy_fmf,

@Rajesh proposal may work, but as a best practice try to avoid calculated columns, unless necessary. Calculated columns can affect overall performance in your model.

Thanks
Jarrett

@JarrettM,

Great stuff. This seems like it would have significant generalizability/applicability to a wide range of scenarios. Thus, I was thinking it might make sense for you to also post the code above with a brief explanation in the M Code Showcase section.

What do you think?

  • Brian

@BrianJ,

All I did was take the revised M code that @Melissa added to the link mentioned in the thread, and added last seven days to it. I guess we could just have @EnterpriseDNA move this thread from “Power BI” to the M Code Showcase section. Let me know what you think. I’m down with anything!

Thanks
Jarrett

Thanks @JarrettM, @BrianJ, @EnterpriseDNA,

Updated the code for the nested Period table in the Dynamic Date Range Slicer thread.

Nice one Jarrett! :+1:

2 Likes