Forum Discussion
Calculating %'s from previous months data
Hi RichOB,
When we want to calculate the number of incidents for a particular Issue and Date combination, we do not need to sum any field. Instead, we should count the number of records (rows) that meet the relevant conditions.
In the original DAX formula provided, where SUM('Table'[Value]) was used, it should be replaced with COUNTROWS('Table'). This function will correctly count the number of rows (incidents) for each Issue and Date, allowing you to then calculate the percentage change over time.
I hope this helped! Feel free to ask any further questions. If this resolved your issue, please mark it as "Accept as Solution" and give us Kudos to assist others.
Thank you.
RichOB , Create a measure using
DAX
% Change =
VAR CurrentIssue = SELECTEDVALUE('Table'[Issue])
VAR CurrentDate = SELECTEDVALUE('Table'[Date])
VAR PreviousDate = CALCULATE(MAX('Table'[Date]), 'Table'[Date] < CurrentDate)
VAR PreviousValue = CALCULATE(SUM('Table'[Value]), 'Table'[Issue] = CurrentIssue, 'Table'[Date] = PreviousDate)
VAR CurrentValue = CALCULATE(SUM('Table'[Value]), 'Table'[Issue] = CurrentIssue, 'Table'[Date] = CurrentDate)
RETURN
IF(ISBLANK(PreviousValue), BLANK(), (CurrentValue - PreviousValue) / PreviousValue)
Hi bhanu_gautam
Thanks for your help. What would the SUM('Table'[Value] refer to?
Hi RichOB,
When we want to calculate the number of incidents for a particular Issue and Date combination, we do not need to sum any field. Instead, we should count the number of records (rows) that meet the relevant conditions.
In the original DAX formula provided, where SUM('Table'[Value]) was used, it should be replaced with COUNTROWS('Table'). This function will correctly count the number of rows (incidents) for each Issue and Date, allowing you to then calculate the percentage change over time.
I hope this helped! Feel free to ask any further questions. If this resolved your issue, please mark it as "Accept as Solution" and give us Kudos to assist others.
Thank you.
Hi RichOB,
We haven’t heard from you on the last response and was just checking back to see if your query was answered.
Otherwise, will respond back with the more details and we will try to help .If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.
Thank you.
Hi RichOB,
I wanted to follow up on our previous suggestions regarding the issue. We would love to hear back from you to ensure we can assist you further.
If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.
Thank you.