Forum Discussion
Solved
Power BI DAX
Hi
I need a DAX to calculate the variance % for etime data.
I am trying to using some date related DAX but it is not helping me.
The variance I am trying to get is
(# of open tickets in previous month - # of open tickets in reporting month) / # of open tickets in previous month
Start of the reporting month is 28 -Feb- 2023
sample data is below:
| Month | Open Requests (Reporting Month) | Open Request (Previous Month) | Variance% |
| Feb-23 | 10206 | ||
| Mar-23 | 10062 | 10206 | 1.41% |
| Apr-23 | 11336 | 10062 | -12.66% |
| May-23 | 17215 | 11336 | -51.86% |
| Jun-23 | 17390 | 17215 | -1.02% |
| Jul-23 | 20903 | 17390 | -20.20% |
| Aug-23 | 20692 | 20903 | 1.01% |
| Sep-23 | 23140 | 20692 | -11.83% |
| Oct-23 | 24764 | 23140 | -7.02% |
| Nov-23 | 23598 | 24764 | 4.71% |
| Dec-23 | 22180 | 23598 | 6.01% |
| Jan-24 | 22566 | 22180 | -1.74% |
| Feb-24 | 24318 | 22566 | -7.76% |
| Mar-24 | 21883 | 24318 | 10.01% |
| Apr-24 | 22906 | 21883 | -4.67% |
| May-24 | 18542 | 22906 | 19.05% |
| Jun-24 | 24589 | 18542 | -32.61% |
Hi Megha2498,
You can create a calculated column below and adjust the output format to percentage:
In plain text:
calc_column_solution = VAR curMonthValue = [Open Requests (Reporting Month)] VAR curMonth = [Month] VAR prevMonth = MAXX ( FILTER ( tbl_with_data, [Month] < curMonth ), [Month] ) VAR prevMonthValue = MINX ( FILTER ( tbl_with_data, [Month] = prevMonth ), [Open Requests (Reporting Month)] ) RETURN DIVIDE ( prevMonthValue - curMonthValue, prevMonthValue )Best Regards,
Alexander
1 Reply
- barritown
Solution Sage
Hi Megha2498,
You can create a calculated column below and adjust the output format to percentage:
In plain text:
calc_column_solution = VAR curMonthValue = [Open Requests (Reporting Month)] VAR curMonth = [Month] VAR prevMonth = MAXX ( FILTER ( tbl_with_data, [Month] < curMonth ), [Month] ) VAR prevMonthValue = MINX ( FILTER ( tbl_with_data, [Month] = prevMonth ), [Open Requests (Reporting Month)] ) RETURN DIVIDE ( prevMonthValue - curMonthValue, prevMonthValue )Best Regards,
Alexander