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

     

     

     

MFelix's avatar
MFelix
Super User
2 years ago

Hi Burtoninlondon ,

 

You are using the Date table from the calendar correct? You mus use the dimension table to get the values on the correct date otherwise you will get this error since the the date from table1 is not directly related with date in table 2.

Burtoninlondon's avatar
Burtoninlondon
Frequent Visitor
2 years ago

Hi MFelix, 

 

I am using the date column from the new Calendar Table as the primary column in my new table. I wanted to display the values from DataSet1 & DataSet2 in the same table...I'm assuming the dimension table is DataSet1 & DataSet2?

 

Is there a way I can dynamically include the values from both DataSets into a single table?

 

Thanks,

 

Neil

 

 

  • MFelix's avatar
    MFelix
    Super User
    2 years ago

    Hi Burtoninlondon ,

     

    The dimension table is the calendar where you have a single row per each value and the other are facts. you relationships are correct.

     

    Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

    If the information is sensitive please share it trough private message.

      • MFelix's avatar
        MFelix
        Super User
        2 years ago

        Hi Burtoninlondon ,

         

        This is happening because you are not doing any aggregation at the facts table, if you change the values to an aggregated level for example SUM everything will work properly: