Measure cannot be filtered by Date Slicer

[owenkosnen]

Hi,

I have a measure that was created via "New Quick Measure" to calculate the QoQ% Change based on a calculated column (just the total of column A + B + C).
I'd like to plot these column and the QoQ% Change into a bar and line chart, and add a Date Slicer. However, it seems that the measure cannot be filtered and displayed by the chart when Date Slicer is being adjusted and causes the whole chart to show error.

[Full date period]

[Adjusted Date period]

As I am new to Power BI, I wonder if anyone can shed some light to what might be the solution for this. I believe it is doable to be done in Power BI, but perhaps there is a trick to make it happen...

Best Regards,

Owen

[TheoC]

Hi @owenkosnen 

You can use the following measures:

QTD = CALCULATE ( SUM ( Table[Amount] ) , DATESQTD ( 'Date'[Date] ) )

Last QTR = CALCULATE ( SUM ( Table[Amount] ) , DATEADD ( 'Date'[Date] , -1 , Quarter ) )

QoQ% = ( [QTD] - [Last QTR] ) / [Last QTR]

Ensure to change the measure [QoQ%] to percentage.

Hope this helps.

Theo 🙂

[TheoC]

@owenkosnen sorry, I had an extra bracket at the end of the QoQ%

QoQ% = ( [QTD] - [Last QTR] ) / [Last QTR] 

[owenkosnen]

Yes I also noticed the extra bracket there but seems it is the issue...
I am ]curious since my base column for (Table[Amount]) is actually a calculated column, could it be the reason why it is not working ?

[TheoC]

@owenkosnen it is likely to do with the relationship you have. Have you established a relationship between the Date table and Fact table. Have you got a Date column in both the Date table and Fact table?

[owenkosnen]

@TheoC there is only one source table for all data so maybe we can opt out relationship setups.. (though I just learned that the best practice should be to keep Date Table separated)

[TheoC]

@owenkosnen how are the dates presented in your table? You will need to change the 'Date'[Date] reference in the measures I recommended to your TableName and [Date] column in order for them to work, subject to you have an appropriate Date column.

Hope that makes sense. 

[owenkosnen]

Hi Theo, 

Thanks a lot for your quick response!
I tried your suggestion to create these measures but not sure why I get Infinity as the value for QoQ%. I have made sure to change it to Percentage as well.
 

[TheoC]

@owenkosnen  sorry, I had an extra bracket at the end of the QoQ%

QoQ% = ( [QTD] - [Last QTR] ) / [Last QTR] 

[Samarth_18]

@owenkosnen , Update QoQ% like below:-

QoQ% = divide( ([QTD] - [Last QTR] ), [Last QTR] ,0)

[owenkosnen]

Hello,

I tried your formula but it is not working unfortunately.. 
I just updated my post with a screenshot for more info, the QoQ is generated with New Quick Measure but just not working with Date Slicer