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 ,

 

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?

    • MFelix's avatar
      MFelix
      Super User
      2 years ago

      Hi Burtoninlondon,

       

      If the two tables have the exact same setup in number a d type of columns I would suggest for you to append them in a single table this would allow for a better modeling approach and more performanr and you would only have a single table to get your data from maybe if you need add a column identifiyying where the rows come from. 

       

      If the tables are not the same the. I would create a measure that wulould Join both values in one single column in the visualization