Date filter hierarchy is showing all months even though the value for that month is not present

[ashuaswinireddy]

Hello All,

I have a date column in my dataset and using that date column-date hierarchy to filter table values.

Date hierarchy silcer is showing all months and days even though the values for those months are not avaiable.

Date column:

Date Filter

We would like to show only 2022 year, October Month, 19 day in above filter example.

Please let me know how to do that.

Thank you!

Regards,

Ashwini

[ashuaswinireddy · Accepted solution]

Below measure worked for me. Thank you for your time @amitchandak 

Created a measure like below, and applied it as a visual level filter on the slicer, filtered to 1.

SlicerFilter = INT ( NOT ISEMPTY ( FactTable ) )

[ashuaswinireddy]

Tried using Date table date column too.. It is also showing all months and days..

[amitchandak]

@ashuaswinireddy , better to create a date table with year, month, day etc., and use that in the slicer 

Calendar = Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
,"Day of Year" , datediff(date(year([DAte]),1,1), [Date], day)+1
, "Month Type", Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY") )
,"Year Type" , Switch( True(),
year([Date])= year(Today()),"This Year" ,
year([Date])= year(Today())-1,"Last Year" ,
Format([Date],"YYYY")
)
)