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
Greenwoodr
Helper I
Helper I

Calculate sum of measure or storing output of measures

Hi, 

 

I have the following issue which I would very much appreciate assistance with.

 

I have the following output in BI

 

FY YearFY MonthActiveNew New Last 12 months
2017P190870118415531
2017P289541166715892
2017P38919966815211
2017P487992152415184
2017P58753777614853
2017P686598131614680
2017P78604292313967
2017P885403100413828
2017P98494887313417
2017P108445888113199
2017P1183790101312798
2017P128342277212601

 

Where New is the sum of a column in my fact table and last 12 months is calculated by a measure

Total New Last12months = CALCULATE (
    [New],
    DATESBETWEEN (
       'Date Dimension'[Date],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Date Dimension'[Date] ) ) ),
        LASTDATE ('Date Dimension'[Date] )
    )
)

As this is a subscription base each row in the fact table represents 1 subscription with dates for starting and finishing which can be used to work out whether each subscriotion is active or closed at a given date and this gives the correct answer.

 

I now need to average the active total at the end of each month and was hoping to use a similar code to the above  but as it is using a measure not a column this is not possible.

 

Does anyone have any ideas as to how to achieve this? I am toying with the idea that these numbers will need to be stored in a new table but am not sure as to the best approach.

 

Many Thanks

Richard

 

 

 

2 REPLIES 2
TeigeGao
Solution Sage
Solution Sage

Hi @Greenwoodr ,

Could you please share the expected result and more information about this problem to us? It would be better if you can share some image to explain it. I think I cannot understand your requirement well.

Best Regards,

Teige

Hi.

 

Thanks for the offer  of help. I have managed to do this now using the summarise function and putting the results in a new table

 

 

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.