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

  • 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.