DAX to calculate relative and total percentage growth based on multiple time selections

Anonymous — Fri, 02 Apr 2021 13:42:00 GMT

I'm trying to calculate few DAX measure to calculate the total balance change between two selected time periods for different codes. The possible outcome would look like:

I have calculated the first 4 DAX measure as follows:

Time Period 1 = VAR _d1 = MIN(data[Date]) RETURN CALCULATE(SUM(data[Balance]), ALLEXCEPT(data,data[Code]),DATESINPERIOD(data[Date], _d1, -6, MONTH)) - CALCULATE(SUM(data[Balance]), ALLEXCEPT(data,data[Code]),DATESINPERIOD(data[Date], _d1, -3, MONTH))

Time Period 2 = VAR _d1 = MAX(data[Date]) RETURN CALCULATE(SUM(data[Balance]), ALLEXCEPT(data,data[Code]),DATESINPERIOD(data[Date], _d1, -6, MONTH)) - CALCULATE(SUM(data[Balance]), ALLEXCEPT(data,data[Code]),DATESINPERIOD(data[Date], _d1, -3, MONTH))

Growth = [Time Period 2]-[Time Period 1]

% Growth = DIVIDE([Growth],[Time Period 2])

I created the following DAX for total growth:

Total Growth = var _d1 = MAX(data[Date]) RETURN CALCULATE([% Growth], data[Date]=_d1,ALL(data))

But I'm sure something's wrong with the formula as I'm not getting any values:

Theoritically 'total growth' is simply the overall growth for all the codes combined(23.21%) in this case which in turn is used to calculate 'relative growth' = % growth - total growth. I'm not able to capture the total growth primarily because it's calculated based on 2 time periods.

Please suggest appropriate modifications to the DAX.

Link to pbix for reference: https://drive.google.com/file/d/14P9YxYbYqEFZ8VSk7cLuR1li4b6g5m9q/view?usp=sharing

Re: DAX to calculate relative and total percentage growth based on multiple time selections

Jihwan_Kim — Fri, 02 Apr 2021 14:35:16 GMT

Hi, Anonymous

I am not sure if I understood correctly, but do you want to show 23.21% for all rows, including the total?

Then, try the below,

CALCULATE([% Growth], ALLSELECTED(data[Code]))

Sorry for my poor understanding.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

Re: DAX to calculate relative and total percentage growth based on multiple time selections

Anonymous — Fri, 02 Apr 2021 14:52:35 GMT

Thanks a lot Jihwan_Kim this worked like a charm. I always get confused with the 'ALL' function family.

topic Re: DAX to calculate relative and total percentage growth based on multiple time selections in DAX Commands and Tips

DAX to calculate relative and total percentage growth based on multiple time selections

Re: DAX to calculate relative and total percentage growth based on multiple time selections

Re: DAX to calculate relative and total percentage growth based on multiple time selections