topic Re: Creating increase/decrease rate with irregular date column in DAX Commands and Tips

Creating increase/decrease rate with irregular date column

gameplane — Mon, 15 Jan 2024 13:08:04 GMT

I have ireegular date values. For example, data before 2023(2021,2021,etc.) only contains December data (integrated), and for 2023, only September and October exist. the cumulative population graph trend is drawn well, but I'm trying to see the rate of change by vomparing the values with the previous month's data, but the dax function doesn't work. It seems to be more difficult because the date values are not sequential. I just need to know the rate of change compared to the previous value of the existing date. Could you please five me some tips?

I wort my own dax function. CurrentDate, currentPopulation, and PreviousDate are returnde well, but only the PreviousPopulation value is not returned well. Why?

---------------------------------------------------------------------------------------

Population Change Rate =

VAR CurrentDate = SELECTEDVALUE('Table'[date])

VAR CurrentPopulation = CALCULATE(

SUM('Table'[population])

'Table'[date] = CurrentDate

)

VAR PreviousDate = CALCULATE(

MAX('Table'[date])

FILTER(

ALL('Table'),

'Table'[date] < CurrentDate

)

VAR PreviousPopulation = CALCULATE(

SUM('Table'[population]},

'Table'[date] = PreviousDate

)

RETURN IF(

NOT ISBLANK(PreviousPopulation),

(CurrentPopulation - PreviousPopulation) / PreviousPopulation,

BLANK()

)

------------------------------------------------------------------------------------------

Please understand that I am not good at English beacuse I am Korean.

Re: Creating increase/decrease rate with irregular date column

Jihwan_Kim — Tue, 16 Jan 2024 03:42:08 GMT

Hi,

I am not sure how your semantic model looks like, but please try something like below whether it suits your requirement.

Population Change Rate = VAR CurrentDate = SELECTEDVALUE ( 'Table'[date] ) VAR CurrentPopulation = CALCULATE ( SUM ( 'Table'[population] ), 'Table'[date] = CurrentDate ) VAR PreviousDate = CALCULATE ( MAX ( 'Table'[date] ), FILTER ( ALL ( 'Table' ), 'Table'[date] < CurrentDate ) ) VAR PreviousPopulation = CALCULATE ( SUM ( 'Table'[population] ), FILTER ( ALL ( 'Table' ), 'Table'[date] = PreviousDate ) ) RETURN IF ( NOT ISBLANK ( PreviousPopulation ), ( CurrentPopulation - PreviousPopulation ) / PreviousPopulation, BLANK () )

Re: Creating increase/decrease rate with irregular date column

gameplane — Tue, 16 Jan 2024 04:17:01 GMT

Thank you soooooooooo much!!!!!!!!!!!!!!!!!!!!