Calculate variance with dates not within page filter range

[Anonymous]

Hi

Below is a sample of a report which I have created in Power BI. The monthly files for this report are uploaded onto one folder stored in the sharepoint and  combined into one dataset which serves as the data feeding into this report.

I am trying to create a card visual to calculate the variance of the Value between the current month (or whichever month selected from the dropdown list) vs. December 2018. Please note that I am not looking at YTD data. I want to calculate the values "AS AT" the month selected vs Dec 2018 (which is always static). Also, I need the card to change accordingly whenever the other filters are applied by the user of this report.

I have tried all the ways which I think might work but is still unable to get it to work most likely due to the "Period" as page filter.

Is there any ways I can create a measure to take the values from the column chart below and use them in my calculation? i.e. based on the chart below Value @ April 2019 = 1.58, Value @ Dec 2018 = 1.34, Variance = (1.58-1.34)/1.34 = 17.9%.

Or is there any other ways to get this to work?

Many thanks in advance for your kind assistance.

[Mariusz]

Hi @Anonymous 

Please see the below Dax Expression 

Sales Dec 2008 = 
VAR dec2008 = CALCULATE(
    [Sales], -- measure you want to calculate
    ALL( 'Calendar' ), -- calendar table (date table)
    'Calendar'[Year Month] = "2008 Dec" -- Calendar table with combined Year and month 
)
VAR sls = [Sales] -- measure you want to calculate
RETURN 
IF( ISBLANK( sls ) = FALSE, DIVIDE( sls, dec2008 ) -1 )

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks @Mariusz  for your prompt reply.

I manage to get the dax to work. Here is the dax I adapted from your suggestion:

Impairment_Variance =

VAR ImpairmentDec2018 = Calculate(

sum('AU Trade Loan Data'[Impairment for Graph]),

all('AU Trade Loan Data'[Reporting Date]),

'AU Trade Loan Data'[Source.Name]="2018-12-AU_Monthly_Reporting.xlsx")

VAR Impairment_SelectedMonth = sum('AU Trade Loan Data'[Impairment for Graph])

Return

if(isblank(Impairment_SelectedMonth)=TRUE,0,Divide (Impairment_SelectedMonth,ImpairmentDec2018)-1)

However, now I have another problem. See the diagram below:

My data contain a few categories. In April 2019, for some reasons, the data only contains 2 out of the

3 categories, i.e. "Pre-Critical" category has dropped out. When calculating the variance using the Dax expression above, it will also exclude those rows in "Pre-Critical" category in the denominator (the result is as 16.67% as per green highlight below). However, I need the denominator to include all rows irregardless of whether the category appears in April or not. The correct variance I am looking for is -5.97%, as per red highlight below.

Is there any ways this can be done please?

[Mariusz]

Hi @Anonymous 

Try the below, make sure you have no page or report filters on category.

Impairment_Variance =
VAR ImpairmentDec2018 = Calculate(
    sum('AU Trade Loan Data'[Impairment for Graph]),
    all('AU Trade Loan Data'),
    'AU Trade Loan Data'[Source.Name]="2018-12-AU_Monthly_Reporting.xlsx"
)
VAR Impairment_SelectedMonth = sum('AU Trade Loan Data'[Impairment for Graph])
Return
if(isblank(Impairment_SelectedMonth)=TRUE,0,Divide (Impairment_SelectedMonth,ImpairmentDec2018)-1)

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Mariusz ,

That's the problem. I do indeed have the category as a page filter.  Is there any workaround?

Regards,

cheols

[Mariusz]

Hi @Anonymous 

Have you tried the code I gave you?

Many Thanks

Mariusz

[Mariusz · Accepted solution]

Hi @Anonymous 

Try Allselected() like below.

Impairment_Variance =
VAR ImpairmentDec2018 = Calculate(
    sum('AU Trade Loan Data'[Impairment for Graph]),
    allselected(),
    'AU Trade Loan Data'[Source.Name]="2018-12-AU_Monthly_Reporting.xlsx"
)
VAR Impairment_SelectedMonth = sum('AU Trade Loan Data'[Impairment for Graph])
Return
if(isblank(Impairment_SelectedMonth)=TRUE,0,Divide (Impairment_SelectedMonth,ImpairmentDec2018)-1)

[Anonymous]

Hi @Mariusz , yes, I have tried it but the denominator returned for Dec 2018 is not right. It takes the sum of everything even after filters are applied. I need it to be dynamic as the users apply the necessary filters.