OK to use Multiple Date Tables?

[riegersn]

I'm creating a series or reports for our financial team. All our reports use data up to the current day. However we have a budget table that has future budget data for the full year. In one of the reports we need to show all this budget data. 

If I create a date table that contains dates for the full year the relative date slicer is inaccurate as current month would always be december and so forth. To fix this I created a date table that stops at the current days actual data. However this results in our yearly report with budgets being cut off at the current month. 

The only solution i see here is to have a second date table with dates that run the full year. Is this ok in power bi, would I run into any issues?

[v-xiaotang · Accepted solution]

Hi @riegersn 

You said "we have a budget table that has future budget data for the full year. In one of the reports we need to show all this budget data.". In this scenario, it's ok to create two separate calendar tables, but not necessary. Here are two examples without creating another calendar table, for your reference,

(1) when there is no relationship between budget table and calendar table,

create measure

test2 = 
    var _start= CALCULATE(MIN('calendar'[Date]),ALLSELECTED('calendar'))
    var _end= CALCULATE(MAX('calendar'[Date]),ALLSELECTED('calendar'))
return 
CALCULATE(SUM(Budget[budget]),Budget[Column1]<=_end && Budget[Column1]>=_start)//

(2) In another scenario, there is a relationship between budget table and calendar table, try this:

Edit interactions,

create measure to get total in the selected period,

test1 = 
    var _start= CALCULATE(MIN(Budget[Column1]),ALLSELECTED(Budget))
    var _end= CALCULATE(MAX(Budget[Column1]),ALLSELECTED(Budget))
return _start&UNICHAR(10)&_end

test2 = CALCULATE(SUM(Budget[budget]),ALLSELECTED(Budget))//

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@riegersn not sure if I will go with two date tables, you can filter the date slicer to show from the current month but still keep the dates up to December, and in the budget measure, you can override the filter to include dates up to the year-end.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡