topic Re: Calculate Percentage Change from First to Last Date in DAX Commands and Tips

Calculate Percentage Change from First to Last Date

BICrazy — Fri, 24 Dec 2021 14:27:54 GMT

Hi the Power BI Community,

I've been able to calculate the YoY percentage change, however, I need to calculate the percentage change from the earliest year (2010) to the latest year(2018) of a data set and display this as a card.

Here are my current calculations which are correct.

The 7.06% calculated below is what I'd like to show as a card visual.

Start	End	Difference	% Change
2010	2018	2018-2010	Difference/2010
80,74	86,44	5,7	7,06%

Thanks

Re: Calculate Percentage Change from First to Last Date

amitchandak — Fri, 24 Dec 2021 14:35:37 GMT

BICrazy , Try measure like

new measure =
var _max = maxx(allselected(Date),Date[Date])
var _min = minx(allselected(Date),Date[Date])
return
calculate( sum(Table[Value Reff]), filter(allselected('Date'), 'Date'[Date]=_max)) -
calculate( sum(Table[Value Reff]), filter(allselected('Date'), 'Date'[Date]=_min))

new measure =
var _max = maxx(all(Date),Date[Date])
var _min = minx(all(Date),Date[Date])
return
calculate( sum(Table[Value Reff]), filter(all('Date'), 'Date'[Date]=_max)) -
calculate( sum(Table[Value Reff]), filter(all('Date'), 'Date'[Date]=_min))

Re: Calculate Percentage Change from First to Last Date

Jihwan_Kim — Fri, 24 Dec 2021 15:36:15 GMT

Hi,

Please check the below picture and the attached pbix file.

All measures are in the attached pbix file.

Re: Calculate Percentage Change from First to Last Date

BICrazy — Sun, 26 Dec 2021 08:00:02 GMT

Jihwan_Kim Appreciate the response Jihwan. This option would have also worked as a solution.

Re: Calculate Percentage Change from First to Last Date

BICrazy — Sun, 26 Dec 2021 08:03:05 GMT

amitchandak Thanks a mill. This solution worked like a charm. I just hard-coded the year's as my dataset min year starts at 1960 and max year ends at 2020.

Here is the sample of my Dax:

% Change GDP per capita, PPP (current international $) = var _min = YEAR( " December 2010" ) var _max = YEAR( "December 2018" ) var _calc =calculate( [Private health expenditure (% of current health expenditure)], filter(ALL( Data_WB_HNP_WDI), Data_WB_HNP_WDI[Year]=_max)) - calculate( [Private health expenditure (% of current health expenditure)], filter(ALL( Data_WB_HNP_WDI), Data_WB_HNP_WDI[Year]=_min)) return DIVIDE( _calc, calculate( [Private health expenditure (% of current health expenditure)], filter(ALL( Data_WB_HNP_WDI), Data_WB_HNP_WDI[Year]=_min)), 0 )

Solution: