Forum Discussion

Burtoninlondon's avatar
Burtoninlondon
Frequent Visitor
2 years ago
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...
Need Help
  • MFelix's avatar
    MFelix
    2 years ago

    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))

     

     

     

Burtoninlondon's avatar
Burtoninlondon
Frequent Visitor
2 years ago

Hi Miguel,

 

I am referring to tables within my model. Your solution worked, thank you for your help.

 

This has introduced another problem for me to solve, would you also be able to assist me with this?

 

I have created a 'many-to-one' relationship between my data sets (DataSet1 & DataSet2 with [Entry time] column containing the dates) and my new calendar table. I linked the 'Date' column from the DataSet1 & DataSet2 column to the 'Date' column in the Calendar Table.

 

 

I have created a basic table on my Report view and I am able to view the values from each data set independantly with the table updating with no errors.

 

 

But when I select the values columns from each dataset to be displayed in this table together as a combined table with all values from both data sets I receive an error message saying the data can't be displayed.

 

 

Apologies if this is a very basic question but can point me in the right direction on how to fix this error? I'd really appreciate any help you can offer.

 

Many thanks,

 

Neil