How to create a calendar using dates from multiple data sets

[Burtoninlondon]

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

•  

[Burtoninlondon]

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]

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]

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]

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.

[Burtoninlondon]

Hi @MFelix, I've uploaded the PBIX file to wetransfer...https://we.tl/t-3TL0p8FaE4

Thanks,

Neil

[MFelix]

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]

@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]

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

[MFelix · Accepted solution]

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