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
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
    • zoloturu's avatar
      zoloturu
      Memorable Member
      6 years ago

      Hi Anonymous ,

       

      I wrote this code without PBIX.

       

      'TableName1'[Week] is your week column ("04-2020", "05-2020")


      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