topic Re: Apply Month Calculation to Row Without Changing Month Calculation in DAX Commands and Tips

Apply Month Calculation to Row Without Changing Month Calculation

BrianNeedsHelp — Wed, 30 Oct 2024 19:52:23 GMT

I have this measure:

MonthChange = sumx(VALUES('Calendar'[Month Year]),[Gross Adds]- CALCULATE([Gross Adds],SAMEPERIODLASTYEAR('Calendar'[Calendar Date])))/sumx(VALUES('Calendar'[Month Year]),[Gross Adds])

The result gives me an overall % change for the month compared to last year as -.11%. I wanting to multiply the -.11% to PYSalesByWeek measure. However [Month Change] will compute by row instead of overall month.

% Applied = [PYSalesByWeek]+([MonthChange]*[PYSalesByWeek])

I'm wanting to remain at -.11. instead of -.08 and -.1.32, respectively. How to accomplish?

PYSalesByWeek	Week Ending Date	Gross Adds	MonthChange	% Applied
2948	10/27/2024 0:00	2722	-0.08	2716.23
2911	11/3/2024 0:00	749	-1.32	-921.10

Re: Apply Month Calculation to Row Without Changing Month Calculation

Irwan — Thu, 31 Oct 2024 00:17:56 GMT

hello BrianNeedsHelp

i think you need to add ALL('table') in your measure so the result will calculate all data.

Thank you.

Re: Apply Month Calculation to Row Without Changing Month Calculation

sanalytics — Thu, 31 Oct 2024 09:59:42 GMT

Hello BrianNeedsHelp
Can you modify the % Applied measure with below code.

% Applied = VAR OverallMonthChange = CALCULATE([MonthChange], REMOVEFILTERS('Calendar'[Calendar Date])) RETURN [PYSalesByWeek] + (OverallMonthChange * [PYSalesByWeek])

Regards

sanalytics

If it is your solution then please like and accept it as solution

Re: Apply Month Calculation to Row Without Changing Month Calculation

BrianNeedsHelp — Thu, 31 Oct 2024 16:25:05 GMT

Hi Irwan It looks like it would work, but it will always apply the logic to the row instead of the overall month. For now I just put in a parameter and adjust the % manually based on what is in the overall month change Card. Thx for replying-if you figure out another way please let me know.

Re: Apply Month Calculation to Row Without Changing Month Calculation

Anonymous — Fri, 01 Nov 2024 08:36:54 GMT

Hi BrianNeedsHelp ， hello sanalytics and Irwan , thank you for your prompt reply!

Please try this:

% Applied = VAR OverallMonthChange = CALCULATE( SUMX(VALUES('Calendar'[Month Year]), [Gross Adds] - CALCULATE([Gross Adds], SAMEPERIODLASTYEAR('Calendar'[Calendar Date]))) / SUMX(VALUES('Calendar'[Month Year]), [Gross Adds]), ALL('Calendar'[Calendar Date]) ) RETURN [PYSalesByWeek] + (OverallMonthChange * [PYSalesByWeek])

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Apply Month Calculation to Row Without Changing Month Calculation

BrianNeedsHelp — Fri, 01 Nov 2024 14:23:17 GMT

Perhaps I can explain it better with the table: The formula you have will return different % based on the row. Let's exclude the calculation you have in the Return and just Return Overall Month Change like I have below.

PYSalesByWeek	Week Ending Date	Gross Adds	% Applied (Return of "Overall Month")
4530	10/6/2024 0:00	2973	-0.09
3673	10/13/2024 0:00	3013	0.05
2917	10/20/2024 0:00	2931	-0.01
2948	10/27/2024 0:00	2722	-0.08
2911	11/3/2024 0:00	1753	0.01

Do you see how that equates to different %'s in each row? That's what I'm trying to avoid. I was trying to get the overall Month Change which is like -2% and multiply -2% * each row. All of the formulas tried result in the exact same -they apply logic to the row. Make sense?

Re: Apply Month Calculation to Row Without Changing Month Calculation

BrianNeedsHelp — Wed, 27 Nov 2024 01:43:12 GMT

Anonymous sanalytics Irwan I finally got this working. To get the sum of the column so that it shows up in each row it's simply

AllFilter1= Calculate([Gross Adds],AllSELECTED()); All PY=Calculate([PYSALESBYWEEK],AllSELECTED());

Then you can do a calculation by row to calculate the overall month change against the previous year(PY) such as:

Forecast1 = [PYSalesByWeek]+(([All Filter1]-[All PY])/[All Filter1])*[PYSalesByWeek]

PYSalesByWeek	Week Ending Date	Gross Adds	All Filter1	All PY	Forecast1
2911	11/3/2024 0:00	2027	10800	13155	2276
4242	11/10/2024 0:00	3075	10800	13155	3317
2942	11/17/2024 0:00	2577	10800	13155	2300
3060	11/24/2024 0:00	3121	10800	13155	2393

I would appreciate kudos and an acceptance as solution please.

Re: Apply Month Calculation to Row Without Changing Month Calculation

Anonymous — Wed, 27 Nov 2024 02:13:18 GMT

Hi BrianNeedsHelp ,

Congratulations on solving this issue and thanks for sharing your solution.

Please remember to accept your solution as answer.

It will do great help to those who meet the similar question in this forum.

Thanks again for your contribution.