Forum Discussion
Calculate variance with dates not within page filter range
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 ser...
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)
Mariusz
Community Champion
Community ChampionHi 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.
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.