Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Previous week value

Hi All,

 

I would like to create a measure value to get previous week value. I dont want to use today-7. Also the below data is for jan 2020 where the data is present in the weekly manner.

Thanks in advance

Need Help

7 Replies

  • zoloturu's avatar
    zoloturu
    Memorable Member
    6 years ago

    Hi Anonymous ,

     

    It can be done in several ways.

    Let us know how do you know what is the number of previous week? Do you base on today date?


    Regards,
    Ruslan Zolotukhin (zoloturu)
    BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
    -------------------------------------------------------------------
    Did I answer your question? Mark my post as a solution!
    It was useful? Press Thumbs Up!

    You are from Ukraine? If yes then welcome to Power BI User Group - KhPUG website. Other country? Check and find proper one - Power BI User Groups

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi zoloturu ,

       

      No Not based on today date,

       

      I would like to have like Weeknum = max(weeknum) - 1.

       

      i.e., previous week data should display in current week data.

       

      Thanks

      • zoloturu's avatar
        zoloturu
        Memorable Member
        6 years ago

        Anonymous ,

         

        Try this one

        Measure Week-1 =
VAR __current_week = ( 'TableName1'[Week] )
VAR __delimiter_position = ( "-", __current_week, 1, 0 )
VAR __previous_week_number = ( LEFT ( __current_week, __delimiter_position - 1 ) ) + 1
VAR __year = ( __current_week, 4 ) 
/* OR use __year = MAX('TableName1'[Year]) if separate column for year exists */
VAR __previous_week = __previous_week_number & "-" & __year

RETURN
    CALCULATE (
        SUM ( 'TableName2'[Sales'] ),
        FILTER ( ALL ( 'TableName1' ), 'TableName1'[Week] = __previous_week )
    )

        You need to replace TableName1, TableName2 with proper table names from your report model.

         

        Regards,
        Ruslan Zolotukhin (zoloturu)
        BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
        -------------------------------------------------------------------
        Did I answer your question? Mark my post as a solution!
        It was useful? Press Thumbs Up!

        You are from Ukraine? If yes then welcome to Power BI User Group - KhPUG website. Other country? Check and find proper one - Power BI User Groups 

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    6 years ago

    Hi Anonymous ，

     

    First, you need to unpivot all your columns: Go to edit queries>Transform>select all the columns(except the column total)>unpivot columns,then you will as below:

    Then  add an index column from 1:

     

    Finally return to the data view and a calculated column using a dax expression as below:

     

     

    Column = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Index]=EARLIER('Table'[Index])-1))

     

     

    And you will see:

     

    For the related .pbix file,pls click here.

     


    Best Regards,
    Kelly

     
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi v-kelly-msft ,

       

      Thanks for your suggestion but by doing unpivot it might affect my other column present in dataset. So is there any way to do it by some other way around.

       

      Thanks