Forum Discussion

HOrakova's avatar
HOrakova
Helper I
1 year ago
Solved

Diff between selected and previous period

Hi Experts, I've closed the previous one as solved, but when I add all filter the formula is not working again. So: I have [Date] column with only start of the week dates in it. I need to calculate...
Need Help
Show and Tell
Tips and Tricks
  • HOrakova's avatar
    HOrakova
    1 year ago

    Hi v-kpoloju-msft  and techies ,

     

    I managed to get the measure to work the way I needed it to:

    Previous Period Value =
    VAR PeriodType = MAX('Period'[Period Type])  
    VAR PeriodsBack = SELECTEDVALUE('No of Periods'[Value])
    VAR PrevRank = [Switch MAX Period Rank] - PeriodsBack

    VAR CurrentMarket = SELECTEDVALUE('data'[Market])
    VAR CurrentCategory = SELECTEDVALUE('data'[Category])
    VAR CurrentBrand = SELECTEDVALUE('data'[Brand])

    RETURN
        IF(
            ISBLANK(PrevRank),
            BLANK(),
            CALCULATE(
                [Switch KPIs],
                FILTER(
                    ALL('data'),
                    SWITCH(
                        PeriodType,
                        "Week", 'data'[Wave] = PrevRank,
                        "Month", 'data'[Month Rank] = PrevRank,  
                        "Quarter", 'data'[Quarter Rank] = PrevRank,  
                        "Year", 'data'[Year Rank] = PrevRank  
                    ) &&
                    'data'[Market] = CurrentMarket &&
                    'data'[Category] = CurrentCategory &&
                    'data'[Brand] = CurrentBrand &&
                    NOT(ISFILTERED('data'[Age])) &&
                    NOT(ISFILTERED('data'[Children]))
                )
            )
        )

     
    Probably there were too many unknowns and I didn't explain things the right way, but I really appreciate your time.

    Thanks a lot!
    HO

techies's avatar
techies
Super User
1 year ago

Hi HOrakova please try this previous period value

 

Previous Period Value =
VAR SelectedDate = MAX(data[Date])  
VAR PreviousDate = CALCULATE(
    MAX(data[Date]),
    ALL(data),
    data[Date] < SelectedDate  
)

RETURN
CALCULATE(
    SUM(data[Value]),
    ALL(data),  
    data[Date] = PreviousDate
)
HOrakova's avatar
HOrakova
Helper I
1 year ago

Hi techies ,
How will look your formula when I use parameter instead of [Date]?

  • techies's avatar
    techies
    Super User
    1 year ago
    Previous Period Value =
    VAR SelectedDate = SELECTEDVALUE('Date Parameter'[Date Selection])  
    VAR PreviousDate =
        CALCULATE(
            MAX('data'[Date]),  
            ALL('data'),  
            'data'[Date] < SelectedDate  
        )

    RETURN
    CALCULATE(
        SUM('data'[Value]),  
        ALLSELECTED('data'),  
        'data'[Date] = PreviousDate
    )
    • HOrakova's avatar
      HOrakova
      Helper I
      1 year ago

      Unfortunately it is not working.
      I uploaded a sanitized copy of my pbix here:
      https://we.tl/t-pbH4A7Azfv
      Hope this helps to understand my case.

       

      Best Regards!

      • techies's avatar
        techies
        Super User
        1 year ago

        Hi, i would suggest using a date table?