How to Compare different country's MTD vs Last Year's MTD in a single stacked bar chart

[Rimi_2095]

Hi All,

I'm having a data where I need to compare MTD and last Years MTD based on country, and also that has to be on single Stacked bar chart. My calculations are listed below.

MTD = TOTALMTD([Total Complaints],pcfinalbi1000[planneddate])

MTD LY = CALCULATE([Total Complaints],DATESMTD(SAMEPERIODLASTYEAR(pcfinalbi1000[planneddate])))

Where, pcfinalbi1000 is my fact table and planneddate is my date column in my fact table.

using this dax I'm getting correct value but when ever I'm putting them on bar chart with country as Axis, it's giving as error "Cast display the visual".

Kindly help me regarding as I can't figure it out how to fix it.

@amitchandak @Ashish_Mathur @Jihwan_Kim @SpartaBI @johnt75 

[SpartaBI]

@Rimi_2095 you can't use time intelligence functions on date columns from your fact table. You need to have a standard date table in your model (or use the auto date/time table functionality but this is not recommended for the long run).
Please read this pattern, it will also give you instructions on an example date table.
https://www.daxpatterns.com/standard-time-related-calculations/

Showcase Report – Contoso By SpartaBI

     

[Rimi_2095]

Hi @SpartaBI 

Thanks for your quick response.

I'm having one dim_date table as well.

Using same dax with date table is not showing accurate value that has to come with calculation.

So in that case what I should do as I need to compare on the basis of country and offcourse with correct outcome?

[SpartaBI · Accepted solution]

@Rimi_2095 impossible to say anything with the info supplied. It can be any kind of reason why you don't get the correct value. The reason for your error was what I said.
To work with time intelligence functions you must use a standard date table. To get the right result you need to write the right code, and it can be complex depending on the way the model is created. I really suggest go and read the pattern and see if you maybe you missed some important code or relationship.