Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
I have a table called Safety Observations and counting the Safety Observations serial number and trying to return the maximum in each month for the past two years compare it with the current Year and add it in a trend line, as per the below image.
For example, in January looking at the past 2 years - the maximum is in 2023 so it should return 30, meanwhile in February the maximum was in 2022 so I want to return 19.
This is the Formula that i have used but its returning the 2024 .
Max Per Year = CALCULATE (COUNT('Safety Observations'[SerialNo]) ,
FILTER ( ALL ( 'Date Dimension' ), 'Date Dimension'[Month] = MAX ( 'Date Dimension'[Month] ) ),
DATESINPERIOD ( 'Date Dimension'[Date].[Date], LAST DATE ( 'Date Dimension'[Date].[Date] ), -2, YEAR )
)
Appreciate your guidance
Solved! Go to Solution.
I have found the solution for this as below:
Created a Measure for getting last years:
I have found the solution for this as below:
Created a Measure for getting last years:
Hi @Oelshamy
You could try. It seems to work
Trend line = CALCULATE(MAX(Inc[Incidents]), ALLEXCEPT('Date', 'Date'[Month Name]))
Joe
Proud to be a Super User! | |
Date tables help! Learn more
Dear Joe, Appreciate your response, I am not sure about your formuala but mine worked as below, i wanted to compare this year with the best performing month in 22 and 23 as below.
Hi,@Oelshamy
We are happy to know that the issue has been resolved. We would appreciate it if you would be willing to share the solution with us to facilitate a solution for the same problem. Additionally, this would be beneficial to other community members as well.
Certainly, if there is anything else we can do for you, please do not hesitate to contact us.
Looking forward to your reply.
Best Regards,
Leroy Lu
Sorry I misunderstood, glad you got it working!
Proud to be a Super User! | |
Date tables help! Learn more
User | Count |
---|---|
24 | |
21 | |
19 | |
13 | |
12 |
User | Count |
---|---|
40 | |
28 | |
28 | |
22 | |
20 |