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.
hello,
I have to compare the total cumulative measures of two years; normal year starting January and a year of work that starts in May, how to calculate them in this case
thank you for your answers and your help
Cheers
Solved! Go to Solution.
Hi @user83 ,
I have create a samle table like this to try to meet your requirement if I got it correctly:
1. Create a calculate column to extract the year of date:
Year = YEAR('Table'[Date])
2. Create the following measures to calculate each year total which are divided into normal month and work month(>=5):
Normal Total of 2019 =
CALCULATE ( SUM ( 'Table'[total] ), FORMAT ( 'Table'[Year], "" ) = "2019" )
Normal Total of 2020 =
CALCULATE ( SUM ( 'Table'[total] ), FORMAT ( 'Table'[Year], "" ) = "2020" )
Work Total of 2019 =
CALCULATE (
SUMX ( FILTER ( 'Table', 'Table'[Date].[MonthNo] >= 5 ), [total] ),
FORMAT ( 'Table'[Year], "" ) = "2019"
)
Work Total of 2020 =
CALCULATE (
SUMX ( FILTER ( 'Table', 'Table'[Date].[MonthNo] >= 5 ), [total] ),
FORMAT ( 'Table'[Year], "" ) = "2020"
)
The final result is like this:
Here is my samle file that hopes to help you, please try it: cumulative total.pbix
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @user83 ,
I have create a samle table like this to try to meet your requirement if I got it correctly:
1. Create a calculate column to extract the year of date:
Year = YEAR('Table'[Date])
2. Create the following measures to calculate each year total which are divided into normal month and work month(>=5):
Normal Total of 2019 =
CALCULATE ( SUM ( 'Table'[total] ), FORMAT ( 'Table'[Year], "" ) = "2019" )
Normal Total of 2020 =
CALCULATE ( SUM ( 'Table'[total] ), FORMAT ( 'Table'[Year], "" ) = "2020" )
Work Total of 2019 =
CALCULATE (
SUMX ( FILTER ( 'Table', 'Table'[Date].[MonthNo] >= 5 ), [total] ),
FORMAT ( 'Table'[Year], "" ) = "2019"
)
Work Total of 2020 =
CALCULATE (
SUMX ( FILTER ( 'Table', 'Table'[Date].[MonthNo] >= 5 ), [total] ),
FORMAT ( 'Table'[Year], "" ) = "2020"
)
The final result is like this:
Here is my samle file that hopes to help you, please try it: cumulative total.pbix
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
The information you have provided is not making the problem clear to me. Can you please explain with an example.
Appreciate your Kudos.
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |