Previous week value

[Anonymous]

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

[v-kelly-msft]

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]

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

[zoloturu]

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]

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]

@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]

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]

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