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.
I want to find the percent change in the following table. However my data is filtered right now to a specific organization and a declined status. How can I calculate the % change and include all of the filters in it?
Thanks!!!
Date | Status | Organization | # of Payments | % Change |
January | Declined | 00 | 75 |
|
February | Declined | 00 | 2 |
|
March | Declined | 00 | 109 |
|
Solved! Go to Solution.
[# Payments PM] =
var __monthInScope = ISFILTERED( DateTable[Month] )
var __monthNumber = VALUES( DateTable[MonthNumber] )
var __priorMonthValue =
CALCULATE (
[# of Payments], -- this should be a measure
DateTable[MonthNumber] = __monthNumber - 1,
ALL( DateTable )
)
var __currentValue = [# of Payments]
return
if( __monthInScope,
DIVIDE(
__currentValue - __priorMonthValue,
__priorMonthValue
)
)
This, of course, works on the assumption that your data model is correctly built, that is, it has a DateTable correctly built and joined to the right Date column in your fact table. If your model is not correctly built, it will not work.
DataTable[Month] column in the DataTable stores the name of the month for each of the dates in DataTable[Date] - January, February...
DataTable[MothNumber] stores the relevant month number in the year - 1, 2, 3,..., 12
Slicing by date can only be done correctly using the DataTable, not the Date field from the fact table. The field should be hidden. In fact, any fields in your fact table should be hidden.
I personally think you should not use the names of the months in your visual. You should use fully qualified unique names of months, like "Jun 2019", "Mar 2018". However, the measure above will work for the first scenario but not for the latter one.
I think you should first sort out your model. Then think about writing correct measures.
Best
Darek
[# Payments PM] =
var __monthInScope = ISFILTERED( DateTable[Month] )
var __monthNumber = VALUES( DateTable[MonthNumber] )
var __priorMonthValue =
CALCULATE (
[# of Payments], -- this should be a measure
DateTable[MonthNumber] = __monthNumber - 1,
ALL( DateTable )
)
var __currentValue = [# of Payments]
return
if( __monthInScope,
DIVIDE(
__currentValue - __priorMonthValue,
__priorMonthValue
)
)
This, of course, works on the assumption that your data model is correctly built, that is, it has a DateTable correctly built and joined to the right Date column in your fact table. If your model is not correctly built, it will not work.
DataTable[Month] column in the DataTable stores the name of the month for each of the dates in DataTable[Date] - January, February...
DataTable[MothNumber] stores the relevant month number in the year - 1, 2, 3,..., 12
Slicing by date can only be done correctly using the DataTable, not the Date field from the fact table. The field should be hidden. In fact, any fields in your fact table should be hidden.
I personally think you should not use the names of the months in your visual. You should use fully qualified unique names of months, like "Jun 2019", "Mar 2018". However, the measure above will work for the first scenario but not for the latter one.
I think you should first sort out your model. Then think about writing correct measures.
Best
Darek
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 |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |