Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX getting previous value (multiple columns)

Hi, 

 

I have a final table like this:

Year Month NameAccount NameContact Full NameValueValue PMDiff
2021-JanBTPeter9 9
2021-AugBTPeter87,890,11
2022-JanBTPeter97,531,47
2022-JanBTAlbert77,53-0,53
2022-AugBTPeter98,420,58
2022-AugBTAlbert68,42-2,42

 

and what I want to do is, getting the previous row value as shown below.
There are multiple columns "Year Month Name", "Account Name" and "Contact Full Name".


I want to get previous values according that columns. How can I handle this?


I created a measure that is working well just for Year Month Name (the [Value PM] values that I'm showing are fine if you put only the Year month Name column, but if I add the other two, the values ​​don't look correct, because they are the same values ​​for Year Month Name, is only considers the Year Month Name column), but I think I need to add the Account Name and Contact Full Name columns to the logic:

Value PM: 

 

 

 

 

CALCULATE (
    [Value],
    FILTER (
        ALL ( Table1 ),
       'Table1'[Date Key]
            = CALCULATE (
                MAX (   'Table1'[Date Key]),
                FILTER ( ALL ( 'Table1'  ),   'Table1'[Date Key]< SELECTEDVALUE (   'Table1'[Date Key] ) )
            )
    )
)

 

 

The Value measure is a simple average measure:

Value Measure = CALCULATE(
AVERAGE([Value]),
FILTER('Table1], 'Table1'[KPI] = "KPI 2")
)

 

Excepted output:

 

 

 

 Thanks a lot !

Need Help
Show and Tell
Tips and Tricks
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi Anonymous ,

     

    If you need calculated column:

    Column =
VAR _a =
    CALCULATE (
        MAX ( 'Table'[Year Month Name] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Account Name], 'Table'[Contact Full Name] ),
            [Year Month Name] < EARLIER ( 'Table'[Year Month Name] )
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Account Name], 'Table'[Contact Full Name] ),
            [Year Month Name] = _a
        )
    )

    Output:

    If you need measure:

    Measure =
VAR _a =
    CALCULATE (
        MAX ( 'Table'[Year Month Name] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Account Name], 'Table'[Contact Full Name] ),
            [Year Month Name] < MAX ( 'Table'[Year Month Name] )
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Account Name], 'Table'[Contact Full Name] ),
            [Year Month Name] = _a
        )
    )

    Output:

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    Hi Anonymous ,

     

    If you need calculated column:

    Column =
VAR _a =
    CALCULATE (
        MAX ( 'Table'[Year Month Name] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Account Name], 'Table'[Contact Full Name] ),
            [Year Month Name] < EARLIER ( 'Table'[Year Month Name] )
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Account Name], 'Table'[Contact Full Name] ),
            [Year Month Name] = _a
        )
    )

    Output:

    If you need measure:

    Measure =
VAR _a =
    CALCULATE (
        MAX ( 'Table'[Year Month Name] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Account Name], 'Table'[Contact Full Name] ),
            [Year Month Name] < MAX ( 'Table'[Year Month Name] )
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Account Name], 'Table'[Contact Full Name] ),
            [Year Month Name] = _a
        )
    )

    Output:

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • anvikuttu's avatar
      anvikuttu
      Advocate I
      2 years ago

      Hi v-jianboli-msft 

      How do we group the above example under "Year Month  Name " field. I mean keep this field and exclude other fields. Is it possible to sum the values shown by Measure without losing the filter context. For eg Aug 2022 should show 9+7= 16 ?

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    This calculated column formula works

    Column = LOOKUPVALUE(Data[Value],Data[Year Month Name],CALCULATE(MAX(Data[Year Month Name]),FILTER(Data,Data[Contact Full Name]=EARLIER(Data[Contact Full Name])&&Data[Year Month Name]<EARLIER(Data[Year Month Name]))),Data[Contact Full Name],Data[Contact Full Name])

    Hope this helps.

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Anonymous , Create a date using month year and join it with date of date table and then you can have measure like 

     

    example measures

     

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
    last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
    last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
    last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
    last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

     

    Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

     

     

    Or you can consider offset

    Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U