Date slicer not working on hierarchy, only MM-YY format

[GillT87]

Hello,

A bit of a strange one, but I'm only starting to use DAX coding so apologies if it is an easy answer. I have a measure written to calculate defect parts per million, and I want this calculation to be interactive depending on the year/qurater/months selected.

I have a problem when I try to make the slicer a date hierarchy, but have my matrix table showing just the MM-YY reference. Is there I can make this work?

Or as another work-around can I make the date hierarchy show both Year and Month in the columns? Currently it will only show the year.

My DAX measure is 

DPPM = IF(SUM('Data'[Nb delivered parts])=0, 0, (SUM('Data'[Nb Defective Parts])/sum('Data'[Nb delivered parts]))*1000000)

And I have a separate table for the date lookup which is linked to my data table.

Thank you in advance for the help!

Gill

[amitchandak]

@GillT87 , Create a date table, join it with date of you table

with columns

Month Year = FORMAT([Date],"mm-yyyy")

Month Year sort = FORMAT([Date],"yyyymm")

Mark the Month Year sort  as sort column of Month Year

[GillT87]

Thank you for your suggestion - unfortunately it did not work for me. Again when I use the date hierarchy from my date table in the slicer, it does not filter the columns you suggested to create in the same date table.