Forum Discussion
Solved
How to create a calendar using dates from multiple data sets
Hello, I am a new user of PowerBI and I need some basic assistance. I want to create a master calendar in my PowerBI model which I can use to link all of my data. I will have multiple data se...
Hi Burtoninlondon ,
When you refer to data set you are refering to tables within your model correct?
For this you can use a syntax similar to this one:
Calendar Table = var _MinDates = UNION({MIN(TableA[date])}, {MIN(TableB[date])},{MIN(TableC[date])}) var _MaxDates = UNION({MAX(TableA[date])}, {MAX(TableB[date])},{MAX(TableC[date])}) Return CALENDAR(MINX(_MinDates,[Value]),MAXX(_MaxDates,[Value]))A best practice suggestion is that your calendar table would be from the 1st of January to 31st of December just redo your previous syntax to:
Calendar Table = var _MinDates = UNION({MIN(TableA[date])}, {MIN(TableB[date])},{MIN(TableC[date])}) var _MaxDates = UNION({MAX(TableA[date])}, {MAX(TableB[date])},{MAX(TableC[date])}) Return CALENDAR(DATE(YEAR(MINX(_MinDates,[Value])), 1, 1),DATE(YEAR(MAXX(_MaxDates,[Value])), 12, 31))
Hi Burtoninlondon ,
When you refer to data set you are refering to tables within your model correct?
For this you can use a syntax similar to this one:
Calendar Table =
var _MinDates = UNION({MIN(TableA[date])}, {MIN(TableB[date])},{MIN(TableC[date])})
var _MaxDates = UNION({MAX(TableA[date])}, {MAX(TableB[date])},{MAX(TableC[date])})
Return
CALENDAR(MINX(_MinDates,[Value]),MAXX(_MaxDates,[Value]))
A best practice suggestion is that your calendar table would be from the 1st of January to 31st of December just redo your previous syntax to:
Calendar Table =
var _MinDates = UNION({MIN(TableA[date])}, {MIN(TableB[date])},{MIN(TableC[date])})
var _MaxDates = UNION({MAX(TableA[date])}, {MAX(TableB[date])},{MAX(TableC[date])})
Return
CALENDAR(DATE(YEAR(MINX(_MinDates,[Value])), 1, 1),DATE(YEAR(MAXX(_MaxDates,[Value])), 12, 31))