cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

Hi All,

Hey DAX Expert i need your help in measure to calculate the value i have 3 measure as mentioned below.

First Measure = "1 Registration"

Second Measure = "1 Termination"

Third Measure = "3 Growth" which is "3 Growth = [1 Registration] - [1 Termination]"

I was to create a Measure called "Running Pre Values"

The Calcualtion for  Running Pre Values are as mentioned below.

2016 Running Pre Values = 2016 growth

2017 Running Pre Values = 2016 Running Pre Values + 2017 growth

2018 Running Pre Values = 2017 Running Pre Values + 2018 growth

2019 Running Pre Values = 2018 Running Pre Values + 2019 growth

2020 Running Pre Values = 2019 Running Pre Values + 2020 growth

Regards

Suhel

7 REPLIES 7
Helper IV

Thank you for your prompt response, the solution is not giving me the end result what I what I am looking i have explanied ine the below screenshot. Please help me I one formula below however it's not giving the result. Thanks

Formula => Running Fleet =
IF(
MIN([Date].[Year]) <= CALCULATE(MAX([Date].[Year]),ALL('Fact Contract')),
CALCULATE(([Registrations]-[Terminations]),FILTER(ALL('Fact Contract'[Date].[Year]),'Fact Contract'[Date].[Year]<=MAX(('Fact Contract'[Date].[Year])))),BLANK())--- it's not working

Regards

Suhel

Super User

@Suhel_Ansari , Better move out the year in a separate table, like second formula.

Formula => Running Fleet =
CALCULATE(([Registrations]-[Terminations]),
FILTER(ALL('Fact Contract'),'Fact Contract'[Date].[Year]<=MAX('Fact Contract'[Date].[Year])))
or
Formula => Running Fleet =
CALCULATE(([Registrations]-[Terminations]),
FILTER(ALL('Date'),'Date'[Year]<=MAX('Date'[Year])))

You can add if as per need

Helper IV

It's not working as seen in the below image.

Regards

Suhel

Super User

@Suhel_Ansari , what is formula for

[Registrations] , [Terminations]

And the Year in visual is also coming from Year Table? Row/group by/Axis should be from Year table

Helper IV

Formula are as seen in below screen shot and the tables view as well. Thanks

Super User

@Suhel_Ansari , Not a good data model. Unpivot the first table create year a custom column in M of the calculated column in Dax

and use that

Super User

@Suhel_Ansari , You can this year and last year values like this, make sure you have YEAR in separate Table or Date Table

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

With help of this you can create other calculations

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors