topic Re: DAX for month on month change in DAX Commands and Tips

DAX for month on month change

Rahul_SC — Sat, 10 Sep 2022 08:34:39 GMT

Hi all,

Can you guide me to create a dax formula for getting month on month change ? I can use calculate(sum, previousmonth(date)) function. but my requirement is slightly differenct.

Actually, I want to find out change in the last week of the each month; like below, I have a table where weekly data is reflecting, I want to find change % of last week of Nov data vs last week of October data then last week of Oct vs last week of September.

Date Value

04/09/2022	13
11/09/2022	54
18/09/2022	23
25/09/2022	44
02/10/2022	55
09/10/2022	32
16/10/2022	67
23/10/2022	33
30/10/2022	68
06/11/2022	23
13/11/2022	56
20/11/2022	89
27/11/2022	65
04/12/2022	43

Thanks

Re: DAX for month on month change

CNENFRNL — Sat, 10 Sep 2022 16:11:13 GMT

Re: DAX for month on month change

Rahul_SC — Sat, 10 Sep 2022 15:47:35 GMT

Hi CNENFRNL ,

Thanks for responding, however, the the value is incorrect.

in the data, last week of Sep (25/09/2022) value is 44 and last week of Oct (30/10/2022) value is 68, so the % change should reflect Sep vs Oct (55%).

And then last week of Oct (30/10/2022) value 68 vs last week of Nov (27/11/2022) value 65, % change should reflect (-4%).

Thanks!

Re: DAX for month on month change

Rahul_SC — Sat, 10 Sep 2022 18:14:13 GMT

Hi CNENFRNL ,

Thanks for the help. I just modified a little to get my required result. It is giving the correct answer.

I got a few questions after going through the attached pbix file and your code. Could you please help help me understand why it is happening ?

1) In the table, where you have created a column (

YYYYMM = YEAR(SRC[Date])*100 + MONTH(SRC[Date]). How the value is coming 2022-09 ? there is no "-" in your code, still in the value it is coming. When I used YEAR(SRC[Date]), it is giving me value like 20-22 ? But when when we use YEAR[Date], it gives year value (2022) in other data model.

2) in your code [ CALCULATE( [Total], SRC[Date] = __lwlm, ALL() ], when I delete 3rd element of the calculate function, it gives error, and says, 3rd argument is compulsory. why ?
In calculate function, only first 2 arguments are compulsory as per microsoft document. I do not know why, we get such behavior.

3) when I tried to sort YYYYMM col by Date col, then it gives me error like attached pick. Why ? what we should do to resolve ?

If you respond, would be very helpful.

Thanks!

Re: DAX for month on month change

daXtreme — Sat, 10 Sep 2022 20:51:54 GMT

For week-based calculations you need a weekly calendar like, say, ISO-week. Please have a look at this: Week-related calculations – DAX Patterns

Re: DAX for month on month change

CNENFRNL — Sun, 11 Sep 2022 08:46:31 GMT

Re: DAX for month on month change

Rahul_SC — Mon, 12 Sep 2022 11:40:25 GMT

CNENFRNL ,

your code is giving the answer, but when I make a relationship with calender table and then replaced date column used in the code with calender date column, it is not working.

I used different code as well which worked with same data set and gave the required result. Again, when I made relationship with calender table then not working. Do not know why ?

One more thing to add, in my source file, there are some repeated dates as well.

by closing balance =

Var Closing_Balance_Previous_Month=CLOSINGBALANCEMONTH(

SUM(SRC[Value]),

DATEADD(SRC[Date],-1,MONTH),ALL()

)

Var Closing_Balance_Current_Month = CLOSINGBALANCEMONTH(

SUM(SRC[Value]),

DATEADD(SRC[Date],0,MONTH),All()

)

return

Closing_Balance_Current_Month/Closing_Balance_Previous_Month-1