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

       

       

      • Burtoninlondon's avatar
        Burtoninlondon
        Frequent Visitor
        2 years ago

        MFelix , I really appreciate your help in resolving this issue for me.

         

        Can I ask another question?

         

        Now that I have the DataSet1 & DataSet2 values in the same table, is there a way to merge the data values into a single column? Would I need to create a new table and somehow create a new column that will append the data in date order?