Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have the following issue:
I want to calculate the 5m average profit (- 2 months from the current month,current month, + 2months).
I've managed to calculate that with a measure (I'm new to DAX, so probably not the prettiest calculation).
average profit of -2 =
VAR maxdate = MAX([date])
AVERAGEX(DATESINPERIOD([date],maxdate,-2,MONTH),CALCULATE(SUM([profit])))
average profit of +2 =
VAR maxdate = MAX([date])
AVERAGEX(DATESINPERIOD([date],maxdate,2,MONTH),CALCULATE(SUM([profit])))
average profit of 5m = CALCULATE(SUM([profit]),[average profit of -2] ,SELECTEDVALUE([date]),[average profit of +2])
Ok, how do I create an aggregated value / YTD from a measure? 😉
You don't. Create a new measure that implements your desired business logic.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
Create calculated column from measure
You cannot create a calculated column or calculated table from a measure.
Date | Profit | |
01.01.2019 | 2 | |
01.02.2019 | 3 | |
01.03.2019 | 4 | |
01.04.2019 | 5 | |
01.05.2019 | 6 | |
01.06.2019 | 9 | |
01.07.2019 | 10 |
My actual table has data covering several years.
What I want is to calculate the rolling average for 5 months of the profit (-2 months, current month, +2months) and from there I would like to calculate an aggregate value.
i.e.
Date | Rolling Average | aggregate Rolling Av | ||
01.01.2019 | ||||
01.02.2019 | ||||
01.03.2019 | 4 | 4 | ||
01.04.2019 | 5,4 | 9,4 | ||
01.05.2019 | 6,8 | 16,2 | ||
01.06.2019 | 6 | 22,2 | ||
01.07.2019 | 5 | 27,2 |
I manage to calculate the rolling average with the formula above. But I haven't managed to calculate the **bleep**. rolling average by re-writing the measure. How do I write a measure that calculates the aggregated rolling average? I also have several years in the model, so i need to consider differentiating by the year as well.
Thanks!
Rolling Avg 5 =
AVERAGEX(
WINDOW( -2, REL, 2, REL,
SUMMARIZE( ALLSELECTED( 'Table' ), 'Table'[Date],"sm",sum('Table'[Profit] )),
ORDERBY( [Date] )
),
[sm]
)
cumul RA =
VAR maxDate = MAX( 'Table'[Date] )
VAR b = SUMMARIZE( ALLSELECTED( 'Table' ), 'Table'[Date], "ra", [Rolling Avg 5] )
RETURN SUMX( FILTER( b, [Date] <= maxDate ), [ra] )
see attached
Amazing!! Thank you so much!
I have two more questions 😉
a) for my understanding - what do you define with the "sm" and "ra" ? since these are not existing columns..
b) is there a way to only aggregate within the year (Jan - Dec) then start again in January the next year..
Thank you!!!
a) those are randomly chosen names for columns in the virtual table created by SUMMARIZE. Feel free to replace with better names.
b) Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.
Thanks so much! I'm still trying to understand the DAX logic.
Here's some sample data and the results I'm trying to achieve. I want each year to be separated in their cumulative results.
Date | Profit | Av 5 Months Profit | Agg. 5m Av Profit | |||
01.01.2019 | 2 | |||||
01.02.2019 | 3 | |||||
01.03.2019 | 4 | 4,00 | 4,00 | |||
01.04.2019 | 5 | 5,40 | 9,40 | |||
01.05.2019 | 6 | 6,80 | 16,20 | |||
01.06.2019 | 9 | 8,40 | 24,60 | |||
01.07.2019 | 10 | 8,20 | 32,80 | |||
01.08.2019 | 12 | 11,60 | 44,40 | |||
01.09.2019 | 4 | 10,80 | 55,20 | |||
01.10.2019 | 23 | 9,60 | 64,80 | |||
01.11.2019 | 5 | 8,40 | 73,20 | |||
01.12.2019 | 4 | 8,20 | 81,40 | |||
01.01.2020 | 6 | 4,80 | 4,80 | |||
01.02.2020 | 3 | 4,40 | 9,20 | |||
01.03.2020 | 6 | 4,80 | 14,00 | |||
01.04.2020 | 3 | 15,00 | 29,00 | |||
01.05.2020 | 6 | 23,60 | 52,60 | |||
01.06.2020 | 57 | 23,00 | 75,60 | |||
01.07.2020 | 46 | 23,80 | 99,40 | |||
01.08.2020 | 3 | 23,20 | 122,60 | |||
01.09.2020 | 7 | 13,00 | 135,60 | |||
01.10.2020 | 3 | 4,60 | 140,20 | |||
01.11.2020 | 6 | 4,80 | 145,00 | |||
01.12.2020 | 4 | 4,60 | 149,60 | |||
01.01.2021 | 4 | 4,60 | 4,60 | |||
01.02.2021 | 6 | 4,60 | 9,20 | |||
01.03.2021 | 3 | 15,20 | 24,40 | |||
01.04.2021 | 6 | 23,60 | 48,00 | |||
01.05.2021 | 57 | 23,40 | 71,40 | |||
01.06.2021 | 46 | 24,00 | 95,40 | |||
01.07.2021 | 5 | 28,50 | 123,90 | |||
01.08.2021 | 6 | 19,00 | 142,90 |
cumul RA =
VAR maxDate = MAX( 'Table'[Date] )
VAR b = CALCULATETABLE(SUMMARIZE(ALLSELECTED('Table'), 'Table'[Date],"ra",[Rolling Avg 5]),YEAR('Table'[Date])=YEAR(maxDate))
RETURN SUMX(filter(b,[Date]<=maxDate), [ra] )
If you insist on having five values to average from then you need to modify the other measure.
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |