Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Debooo1
Regular Visitor

Create calculated column from measure or YTD from measure

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).

 

AvProfit5m =

VAR NumMonthsPre = 3
VAR NumMonthsPost = 2
VAR RollingSumPre =
    CALCULATE(

            SUM('P&L all (2)'[Profit]),
DATESINPERIOD('Calendar'[Date],EOMONTH(MAX('Calendar'[Date]),0),-NumMonthsPre,MONTH)
        )

VAR RollingSumPost =
    CALCULATE(
        SUM('P&L all (2)'[Profit]),
DATESINPERIOD('Calendar'[Date],LASTDATE('Calendar'[Date]),NumMonthsPost,MONTH)
        )

RETURN
{RollingSumPost+RollingSumPre}/5
 
However, when I want to use YTD on top of this measure, it doesn't work and doesn't accumulate the values.
I assume I would have to calculate the 5m average in a calculate column ? 
When I use the same calculation to add a new column, it doesn't work. Why?
 
Is there a way to get the correct results in a column from which I can create a YTD measure or another way to write a measure on top of the existing one that will accumulate my values?
 
Thanks,
Deborah
10 REPLIES 10
devanshi
Helper V
Helper V

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])

 

Debooo1
Regular Visitor

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...

lbendlin
Super User
Super User

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] )

 

 

lbendlin_0-1685584979858.png

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors