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.
@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.
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.
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!