SELECTEDVALUE Context with Slicer

[Jacon25]

Perhaps I've been going about this the wrong way, but I've been struggling with the following DAX measure.

ShowData =
VAR yesterday = CALCULATE(MAX(AllDates[Date]), ALLEXCEPT(AllDates, AllDates[Date])) - 1
VAR DaysAgo52 = CALCULATE(MAX(AllDates[Date]), ALLEXCEPT(AllDates, AllDates[Date])) - 53
VAR WeeksAgo52 = CALCULATE(MAX(AllDates[Date]), ALLEXCEPT(AllDates, AllDates[Date])) - 369 - WEEKDAY(CALCULATE(MAX(AllDates[Date]), ALLEXCEPT(AllDates, AllDates[Date])))
VAR yesterdayWeek = CALCULATE(MAX(AllDates[Date]), ALLEXCEPT(AllDates, AllDates[Date])) + 1 - WEEKDAY(CALCULATE(MAX(AllDates[Date]), ALLEXCEPT(AllDates, AllDates[Date]))) - 6

VAR result52Days = IF(AND(SELECTEDVALUE(AllDates[Date]) >= DaysAgo52, SELECTEDVALUE(AllDates[Date]) <= yesterday), 1, 0)
VAR result52Weeks = IF(AND(AND(SELECTEDVALUE(AllDates[Date]) >= WeeksAgo52, SELECTEDVALUE(AllDates[Date]) <= yesterdayWeek), WEEKDAY(SELECTEDVALUE(AllDates[Date])) = 2), 1, 0)

RETURN IF(FIRSTNONBLANK(PeriodFilter[Period], 0) = "Daily", result52Days, result52Weeks)

I'm trying to create a measure that will determine what dates should be shown in a graph in depending on if a daily or weekly granularity is selected by a slicer. The issue is I also have a date range slicer for AllDates that narrows the window of time the user wishes to view.

If I replace the "ALLEXCEPT" with "ALL", then the measure works, but my AllDates date slicer will no longer filter.

I seem to be stuck in a catch 22 with this. Is it possible to ignore the row context with "ALL" and also have my date slicer work?

Thanks!

[v-easonf-msft · Accepted solution]

Hi , @Jacon25 

Since there is no sample data for test, it is difficult to clarify your problem specifically

Have you tried to  replace the "ALLEXCEPT" with "ALLSELECTED"?

Best Regards,
Community Support Team _ Eason

[Jacon25]

Yes, that is exactly what I was looking for. I wasn't familiar with that DAX function. Thanks!

[amitchandak]

@Jacon25 , I think these measure should be created like following examples

Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))
Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

WTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[Weekday] <=max('Date'[Weekday])))
LWTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -1) && 'Date'[Weekday] <=max('Date'[Weekday])))


LYWTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52) && 'Date'[Weekday] <=max('Date'[Weekday])))

LYWTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=(max('Date'[Year]) -1)
&& 'Date'[Week Number]=(max('Date'[Week Number]))
&& 'Date'[Weekday] <=max('Date'[Weekday])))

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.