Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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 ()
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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 ()
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thank you soooooooooo much!!!!!!!!!!!!!!!!!!!!
User | Count |
---|---|
53 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
92 | |
86 | |
39 | |
24 | |
22 |