How to apply dynamic filter on customised date table | To filter only corresponding values |

[MYDATASTORY]

Hi, 

Please advice on DAX calculation of dynamically filtering values from the customised calendar which has Year(YYYY), Quater, Month(MMMM) and Date column 
I would when a Year filter is applied only show Dates which fall on that year on Date Filter

When the Quarter filter  is selected only show Date which falls on that  selected Quater range

I have below columns on customized calendar |Date|Quarter|Month Number|Year|

Thanks in advance.

[amitchandak · Accepted solution]

@MYDATASTORY , question not clear

Example of April to March calendar

https://www.dropbox.com/s/wrcyk5j66corvjg/Apr2Mar-Cal.pbix?dl=0

// Standar cal
Date = CALENDAR(Date(2018,01,01),TODAY())
Mark as Date Table
Is Today = if('Date'[Date]=TODAY(),"Today",[Date]&"")
Month Year New =if([Date] >=Switch( True(),
eomonth([Date],0) >= eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0) >= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY")
)
Month Year = FORMAT([Date],"mmmm-yyyy")
Month Year sort = FORMAT([Date],"yyyymm")
Month Rank = RANKX(ALL('Date'),'Date'[Month Year Sort],,DESC,Dense)
Set Sort Column
Year = FORMAT([Date],"YYYY" )

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Number = WEEKNUM([Date],2)
Week = if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number])
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
Week name = [Week Start date] & " to "& [Week End date]
Weekday = WEEKDAY([Date],2)
WeekDay Name = FORMAT([Date],"ddd")

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[AllisonKennedy]

I'm not sure I understand the problem. Have you tried using the hierarchical slicer or editing interactions between the slicers? https://powerbi.microsoft.com/en-us/blog/tag/hierarchy-slicer/