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 sets (imported from individual excel files) and each contain a series of dates. I know how to use the CALENDAR function to create a calendar using the MIN date and MAX date from a single data set but how can I create a calendar using the earliest date from all data sets to the latest date from all data sets?

 

For example...

  • data set 1 might contain data for 10 dates between 1st Feb 24 to 4th Mar 24
  • data set 2 might contain data for 15 dates between 15th Jan 24 to 25th Feb 24
  • data set 3 might contain data for 45 dates between 31st May 23 to 2nd Jan 24

I'd therefore want my master calendar to range from 31st May 23 to 4th Mar 24.

 

Any help is appreciated.

 

Thanks,

 

Neil

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

     

     

     

9 Replies

  • MFelix's avatar
    MFelix
    Super User
    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