master calendar doesn't work

[wanyunyang]

Hi guys,

I have a master_calendar table with Year, Month, Date, and LINK_DATE (Date in master_calendar table) which links to sales table by LINK_DATE (sales_date in sales table). Now I'm trying to calculate Previous Year's sales amount. My function is:

Sum({<Date={">=$(=addyears(Min(Date),-1))<=$(=addyears(Max(Date),-1))"}>}[SalesAmount])

I have filters: Year, Month, Date, Date as Date Range Picker. When I filter with month or date or date range picker, it works. But when I filter with year, it only shows me 0.

I'm wondering what's wrong. Any advice helps. Thanks in advance!

[kaushiknsolanki · Accepted solution]

Hi,

What I see missing in this expression is you haven't ignored the selected fields of master cal.

Meaning if you have selected Year then it will calculate the expression correctly, but by default the Identifier is the current selection. So the expression will try to evaluate with in the selection you have made, and that's where the conflict happens and you get 0 as result.

So it is important that you ignore the Year selection in set analysis. Something like below.

Sum({<Date={">=$(=addyears(Min(Date),-1))<=$(=addyears(Max(Date),-1))"},Year=>}[SalesAmount])

Hope this helps.

Regards,

Kaushik Solanki

[wanyunyang]

Thank you so much!

[kaushiknsolanki]

Cheers..

Regards,

Kaushik Solanki

[pwagner]

Nice, thank you for your solution.

This helps if you use 'Date Range Picker' and you want to show the data of previous years of the data picked in the extension.

Best regards, Patrick