The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!!!!!!!!!!!!!!!!!!!!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
67 | |
36 | |
21 | |
18 | |
15 |
User | Count |
---|---|
124 | |
39 | |
30 | |
24 | |
22 |