Forum Discussion
Multiple Date Frequencies and Formats in One column
Hi All,
I have a model connected to the DHIS database using a custom connector. I have two date tables imported from the database. A date table which stores daily data and a period table which has weekly, monthly and yearly data date frequencies. My facts table has only one Period coulmn that accomodates both date/period types. How do I create a combined date table that filters for both daily and other data periods as I currently use different date filters and neither filters the other. I have attached screen grabs of the date table, period table and fact table period columnDate TablePeriod TableFacts Table
4 Replies
- amitchandak
Super User
MukhtarDotun , On Idea I have, is to pivot the data using period and period value. Does not seem great for this another one is converting everything to date
DAX code - New column , we can have in power query too
Switch(True() ,
[Interval] = "Year", Date([period],1,1)
[Interval] = "Quarter", Date(left([period],4) ,right([period],1)*3 ,1), // or Date(left([period],4) ,right([period],1)*3 -2 ,1)
[Interval] = "Month", Date(left([period],4) ,right([period],2)*1 ,1)
[Interval] = "FY", DATEVALUE("01-"&right([period],3)&"-"& left([period],4) )
)
For week refer last question in blog
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
Thank you for your feedback. So the problem is now that on the Facts table Period Column the formats there are not only daily dates but also 202009 format for months, 2021W49 format for weeks, 2021 format for Annual years, 2019Oct format for fiscal year. Any luck on the expressiion for converting all these formats to dates similar to that above
amitchandak Thank you for your feedback. So the problem is now that on the Facts table Period Column the formats there are not only daily dates but also 202009 format for months, 2021W49 format for weeks, 2021 format for Annual years, 2019Oct format for fiscal year. Any luck on the expressiion for converting all these formats to dates similar to that above
V-lianl-msftCommunity Support
Hi MukhtarDotun ,
If have a 1-to-many relationship on DateKey, try to create a new date column in the fact table:
Date = RELATED('Date'[Date])
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
