DAX Formula: how to calculate percent change in columns with filters?

[Anonymous]

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

[Anonymous · Accepted 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