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 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.
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)
7 Replies
- Mariusz
Community Champion
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.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])Returnif(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 the3 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.