Need Advise on how write DAX to calculate Week on Week Changes to compare the same month

[NH]

Hi,

Seeking someone to advise/help  on how to create DAX formulae to calculate the Delta btw the data from previous week.

compare to current week. My sample table as below

Example is I want to compare the delta of Part No A based on date pulish on 5/10/2016 and 5/17/2016 for May 16, Jun 17, Jul 16

Date	Part No.	May-16	Jun-16	Jul-16
5/10/2016	A	11	11	10
5/10/2016	B	12	12	12
5/10/2016	C	13	14	13
5/10/2016	D	16	15	14
5/10/2016	E	25	20	20

Data	Part No.	May-16	Jun-16	Jul-16
5/17/2016	A	10	12	10
5/17/2016	B	12	13	12
5/17/2016	C	13	14	14
5/17/2016	D	16	15	16
5/17/2016	E	20	20	25

Result Delta
Part No.	May-16	Jun-16	Jul-16
A	-1	1	0
B	0	1	0
C	0	0	1
D	0	0	2
E	-5	0	5

Thanks

NH

[waltheed · Accepted solution]

Hi NH,

First change the table structure a bit, to get rid of months as column names, but put them as values in the rows. Then your model is also future-proof. Also make sure the date is a proper date type column.

Then, in your model add 2 calculated columns:

Column: PrevValue

= CALCULATE(MAX([Value]);

(FILTER(MyTable;

EARLIER(MyTable[Part No.])=MyTable[Part No.] &&

EARLIER(MyTable[YearMonth])=MyTable[YearMonth] &&

EARLIER(MyTable[PublishDate])>MyTable[PublishDate]

)))

Column: DeltaValue

= [Value] - [PrevValue]

That should do the trick.

[NH]

HI Waltheed,

Sorry need to trouble you again.

I've encountered issue with the DAX formaule below on the Max function.Where the Prevalue alway take the Max value of the past few weeks and not take latest previous week value.

I did try to change to Min but encountered similar issue. Is there other function beside using Min or Max function?

Example:

Date of 5/23/2016, Month Year = May-16

Pre value for part A  should be = 10 but actual in in Power BI DAX it was = 11 (belong to the date 5/10/2016)

Date	Part No.	May-16	Jun-16	Jul-16
5/10/2016	A	11	11	10
5/10/2016	B	12	12	12
5/10/2016	C	13	14	13
5/10/2016	D	16	15	14
5/10/2016	E	25	20	20
Data	Part No.	May-16	Jun-16	Jul-16
5/17/2016	A	10	12	10
5/17/2016	B	12	13	12
5/17/2016	C	13	14	14
5/17/2016	D	16	15	16
5/17/2016	E	20	20	25
Data	Part No.	May-16	Jun-16	Jul-16
5/23/2016	A	9	12	10
5/23/2016	B	12	13	12
5/23/2016	C	13	14	14
5/23/2016	D	16	15	16
5/23/2016	E	20	20	25

CALCULATE(MAX([Value]);

FILTER(MyTable;

EARLIER(MyTable[Part No.])=MyTable[Part No.] &&

EARLIER(MyTable[YearMonth])=MyTable[YearMonth] &&

EARLIER(MyTable[PublishDate])>MyTable[PublishDate]

)))

[waltheed]

Hi NH,

Did you create the table structure as explained?

Also make sure that the date column is a proper date field, and that the YearMonth field is numeric.

This should give you the correct sorting order, so that the MAX function can work.

[NH]

Hi Waltheed,

I think the problem was still caused by calcualate (Max[value] function where it alway take the highest value in the [value] data.

I've used Power BI Desktop and created the table strucutred as shown in the pictures below. But I'm not able to convert the MonthYear to Numeric as in Power BI there wasn't has this fomrat function. I've change it to MMMM YYYY format..

The result was for publish date on 5/23/16, The prevalue was 11 which was taken from the 5/10/16 publish date. This was wrong , as it should take previous publish date on 5/17/16 data of 10.

Thus appreaciate if you can advise or help to resolve this problem as I need this solution urgently for my project.

Thanks.

 

[waltheed]

Hi NH,

I'm sorry, but you have to create a numeric (or text) column with year-month, which sorts in the correct order. That makes the EARLIER and MAX function work properly. You can do this in your data model,  but also in the query. You can add it as additional column, you don't have to replace the existing month column.

To create such a column in your data model you could do something like this:

Column YearMonth:

= Year([Date]) * 100 + Month([Date])

That would give you 201601, 201602, 201603, etc...

[NH]

Hi Waltheed,

I've tried your suggested solution but result still the same. I've used althernative method to resolve it which compute the  pre week value in the table to get over this issue.

Thanks for your help and advise.

Regards,

NH

[NH]

Hi Waltheed,

Yes I've created the table structure as per your advice.
The only difference was the yearmonth column was formats as date and not numeric.

Regard
NH

[NH]

Hi Waltheed,

Thanks for the advise and solution as I've tried out and it work.

Kudos to you.

Best Regards,

NH

[ankitpatira]

@NH Just a suggestion. One way to do this without DAX woould be to Merge both queries using Part No column. Then just create a new column using simple Table1[May-16] - Table1[Merged.May-16] formula.

[NH]

Hi Ankitpatira,

Thanks for your advise.

May be I didn't specific clearing. The two table has merged in single table already. I just need to find a way to use DAX to do calculation of the Week on Week delta based on the spectific month.

Every weeks the new data with rolling 3 (May, Jun, Jul) months forecast data will be append to the same table (merged table).

Thanks

NH.