How to display current week vs previous week % variance on a card when slicer has only 1 selection

[Chazzo]

Hello All,

I'm new to to the forum/new to using DAX and am having difficulties in displaying week over week % variance on a PBI card when only 1 week is slected in my slicer. 

My objective is to show current week vs previous week % variance based on the users week selection.  For example, if week 23 is selected in the week slicer, I want to compare week 23 vs week 22 and display % variance on the card.

If there are no filters on the week slicer, or non consecutive weeks selected, I want to display the max week vs the previous week % variance on the card.

Below are the DAX formulas I have for current week and previous week.  When selecting 1 week in my slicer, my result is returning (Blank).  I belive this is simply because Previous week result is being filtered out. 

Thank you in advance for any solutions you may have to offer!

  

[Anonymous · Accepted solution]

Hi, @Chazzo 

Thanks for @aduguid positive reply. You can try this measure to solve your need.

Measure:

% Sales = 
VAR _maxSelectedWeekNum =
    CALCULATE ( MAX ( 'Week slicer'[WeekNum] ) )
VAR _currentWeekSales =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        'Table'[WeekNum] = _maxSelectedWeekNum
    )
VAR _previousWeekSales =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        'Table'[WeekNum] = _maxSelectedWeekNum - 1
    )
VAR _result =
    DIVIDE ( _currentWeekSales, _previousWeekSales )
RETURN
    _result

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[aduguid]

Try this measure

WeekOverWeekVariance = 
VAR SelectedWeek = SELECTEDVALUE(tbl_FF[Week])
VAR MaxWeek = MAX(tbl_FF[Week])
VAR CurrentWeekValue = 
    CALCULATE(
        [_Tot Sales TY],
        tbl_FF[Week] = SelectedWeek
    )
VAR PreviousWeek = 
    IF(
        ISBLANK(SelectedWeek),
        CALCULATE(
            MAX(tbl_FF[Week]),
            FILTER(
                ALL(tbl_FF),
                tbl_FF[Week] < MaxWeek
            )
        ),
        SelectedWeek - 1
    )
VAR PreviousWeekValue = 
    CALCULATE(
        [_Tot Sales TY],
        tbl_FF[Week] = PreviousWeek
    )
RETURN
IF(
    ISBLANK(CurrentWeekValue) || ISBLANK(PreviousWeekValue),
    BLANK(),
    DIVIDE(CurrentWeekValue - PreviousWeekValue, PreviousWeekValue, 0)
)

[Chazzo]

Hi Aduguid,

This measure is working great when selecting a single week (thank you so much!).

The part that doesn't seem to be working is:

"If there are no filters on the week slicer, or non consecutive weeks selected, I want to display the max week vs the previous week % variance on the card."

Do you have a suggestion on how I might modify the measure to show the variance of the max week vs previous when slicer has no filter or multiple weeks selected?