Forum Discussion
How to apply dynamic filter on customised date table | To filter only corresponding values |
- 6 years ago
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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.