Forum Discussion

bml123's avatar
bml123
Post Patron
4 years ago

Get previous week value with a condition

HI,

 

I have data like this as below. For each date, I want to get the  amount of the previous week where Active column is 'Yes'.

 

DateAmountActiveWeekRank
28/01/2022100Yes4
01/02/2022-50 5

 

How do I achieve that?

Need Help

4 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    Hi bml123 

     

    Try this:

     

    Previous Week =
    Var _Current_Week = Max('Table'[WeekRank])
    Var _Last_Week = _Current_Week-1
    return
    Calculate(Max('Table'[Amount]),filter(All('Table'),'Table'[WeekRank]=_Last_Week&&'Table'[Active]="Yes"))
     
     
    Output:

     


     

     

    Sample file attached.

     

     

    Check this link for more details:

     

    https://www.vahiddm.com/post/weekly-time-intelligence-dax

     

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/

     

     

    • bml123's avatar
      bml123
      Post Patron
      4 years ago

      Hi VahidDM 

       

      WeekRank is there in a separate calendar table and is not part of 'Table'.

      How do I change the formula to reflect that?

       

      • VahidDM's avatar
        VahidDM
        Super User
        4 years ago

        bml123 

         

        Can you share a sample of your tables in a text format? and let us know the relationship between tables?

         

        BTW, the easiest way is to use lookupvalue to add a WeekRank column to the main table.

         

        If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
        Appreciate your Kudos!!
        LinkedIn:         www.linkedin.com/in/vahid-dm/

         

         

  • v-jinweili-msft's avatar
    v-jinweili-msft
    Microsoft Employee
    4 years ago

    Hi bml123 ,

     

    1. I have created a  calendar table with two columns, Date and WeekRank for slicer.

    Calendar =
ADDCOLUMNS (
    CALENDAR ( MIN ( 'Table'[Date] ), MAX ( 'Table'[Date] ) ),
    "WeekRank", WEEKNUM ( [Date], 2 )
)

     

    2. Create a measure to sum of the week.

    Sum of last week =
VAR _week =
    MAX ( 'Calendar'[WeekRank] ) - 1
RETURN
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Active] = "Yes"
                && WEEKNUM ( 'Table'[Date], 2 ) = _week
        )
)

     

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