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. T...
Need Help
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 

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi zoloturu 

         

        Could you please attach the Pbix file for the below code which you tried?

        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 )
    )

         I am gettting so many doubts thats why.

         

        'TableName1'[Week] is a measure?
         
        Thanks