Forum Discussion

Jacon25's avatar
Jacon25
Frequent Visitor
6 years ago
Solved

SELECTEDVALUE Context with Slicer

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!
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    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

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


    Appreciate your Kudos.

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Jacon25
      Frequent Visitor
      6 years ago

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