Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I need a DAX function that calculates the % change between the bars on this running total chart. The first bar will not have a change because the prior period needs to start at zero no matter what range of years the user selects.
Here is my running total function
running test cy = IF (
[Actuals],
CALCULATE (
[Actuals],
FILTER (
ALLSELECTED ( 'Calendar'[Date] ),
ISONORAFTER ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), DESC )
)
)
)
Here is the current bar chart and some mark up indicating what I am looking to do.
2008 % Change = (.3-0) / 0) = 0 or N/A
2009 % Change = (.7-.3) / .3) = 133%
2010 % Change = (1-.7) / .7 = 43%
so on and so forth up to the last year
Thanks in Advance for any help!
@BlueNote , Based on what I got
YOY % will the change % even in case of cummulative
Create measures like this year vs last year (or as var ), by any og the one method
examples
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])
Thanks for your reply.
I tried using simple YoY % change but that doesn't work because this is % change of a cumulative amount. Also the first year needs to start with the prior year = 0 in all date ranges selected by the user.
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |