Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Please help me. Thanks
Regards
Suhel
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
Please assit. Thanks
Regards
Suhel
@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
@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
@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
https://radacad.com/pivot-and-unpivot-with-power-bi
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |