Calendar Sheets vs Data Source

[thibbos]

Hi all,

read text below as having to query tables: one CALENDAR TABLE and one DATA table

I have worked with CALENDAR tables for a while now. Mostly data i used was daily, making it fairly simple to link the CALENDAR table dates to the DATA dates. Now i am working for a company where data is only by weeks or months. 

What I do is that I use a full calendar table and in theDATA month column, I add a new column with a specific date of that month.

As such it works, but I often face the issue that because all dates in my DATA are only contiguous in a sense that they follow each other but with lots of dates in between making it difficult to use Time Intelligence Functions.

Either do I miss something on Time Intelligence, either I need to make specific calendar tables for MONTH data or for WEEK data. Then I am not even talking about combining weekly ands monthly data.

So questions are:

1. Have you got any advise on how to handle data that is only available onn MONTHLY/WEEKLY Base?

2. How would you build your calendar table?

3. Do you build in between mapping tables to avoid "many to many relationships"

4. Can you point me out to a video/webinar explaining these particular issue? I have watched a few but they all start from DATA being available with day dates.

Thanks  a lot, i hope i made myself understood

Thibault

[amitchandak · Accepted solution]

@thibbos , I would have created date from week and month and then it can work. Week to date will depend on the format.

In this blog I have discussed one way -https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3

for month Start date and the end date has its own role - refer to this video how to get dates from month -First 3-4 min should give you that -https://www.youtube.com/watch?v=yPQ9UV37LOU

[PaulDBrown · Accepted solution]

@thibbos 

Here are two options:
1) Add a date field to your data table based on the  corresponding beginning or end of week/month date and then use this date field to link to the Calendar Table
2 ) Use a "Period" table instead of a calendar table. Time Intelligence won't work, but you can code the DAX as an alternative. In this case you will need a field for either WWYYYY or MMYYYY in both the period table and your data table to establish the relationship.

[thibbos]

@PaulDBrown @amitchandak thanks both of you. Based on your responses you are saying you would make specific tables and using dax to "manually" create time intelligence. Oke that's what I am doing so far. Was just wondering if there were maybe any other methods.

I a have specific "date tables" for weekly data (recalculating "Last Week" "LAst 4 weeks" for example) and monthly data. I created these tables in excel (automatic formulas) which i adapt according to each report needs if necessary.

Also it gives me one extra advantage, I have to update the date sheet manually when there is new data available. But that way, I am still very flexible.

thanks az lot

[amitchandak · Accepted solution]

@thibbos , I would have created date from week and month and then it can work. Week to date will depend on the format.

In this blog I have discussed one way -https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3

for month Start date and the end date has its own role - refer to this video how to get dates from month -First 3-4 min should give you that -https://www.youtube.com/watch?v=yPQ9UV37LOU