Forum Discussion

BrianNeedsHelp's avatar
BrianNeedsHelp
Icon for Resolver I rankResolver I
1 year ago

Apply Month Calculation to Row Without Changing Month Calculation

I have this measure:         MonthChange = sumx(VALUES('Calendar'[Month Year]),[Gross Adds]- CALCULATE([Gross Adds],SAMEPERIODLASTYEAR('Calendar'[Calendar Date])))/sumx(VALUES('Calendar'[Month Y...
Applying Calculated Result to Rows
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.

BrianNeedsHelp's avatar
BrianNeedsHelp
Icon for Resolver I rankResolver I
1 year ago

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  
291111/3/2024 0:00202710800131552276
424211/10/2024 0:00307510800131553317
294211/17/2024 0:00257710800131552300
306011/24/2024 0:00312110800131552393


I would appreciate kudos and an acceptance as solution please.  

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.