Fact outside of date range

Power BI
1

My corporate calendar has 2017 to 2022.
My Budget data has values past 2022.
My Sales records have values before 2017.

One option is to obviously only take data that matches an entry in the calendar.
One option would be to extend the calendar (really dont want to do this as the calendar is a little crazy)

The final option is to add two lines to the calendar with Surrogare keys of -99999999 and +99999999.

Then I need to look at the surrogate key in the Budget table and if it does not exist in the date table (i.e sk_budget_date >MAX('dim Date[sk_date]) then +99999999

This then needs to happen on the other tables.
I want to keep it as low impact as possible.

I could in the Budget Query merge the date Query to get the sk_date column (joined on sk_budget_date = 'dim Date[sk_date]). If the retuerned field is NULL then +99999999. But I think this may add a lot of overhead is there a better way?

Thanks
E

2

Hi @ells,

Give this a go.

image

Next “right click” in the Min field and select “Add as new query”
image

Repeat the two steps above for the Max value as well…

And add a Custom Column to all relevant tables with something like this.

image
image1152Ă—274 12.9 KB

Please note that if you add these surrogate keys to your Dates table, I expect you can no longer validate aka mark it as a date table because that requires a contiguous range…

Work around suggestion. Add 31-12-2016 for surrogate key -99999999 and 1-1-2023 for surrogate key +99999999 then exclude both first- and last row from the Date column before you pass it to Table.Profile like so:

Table.Profile( Table.RemoveLastN( Table.RemoveFirstN( Dates[[Date]], 1), 1) )

I hope this is helpful.

1 Like
3

That’s a super cool trick - never seen that one before. Learn something new every day…

Thanks!

  • Brian
4

Well another simple trick I guess would be to just add or subtract 1 date from the min/max result…

if [Dates] < Date.AddDays( MinDate, 1 ) then -99999999 else 
if [Dates] > Date.AddDays( MaxDate, -1 ) then +99999999 else 
[Dates]
1 Like
5

@Melissa
Thanks that looks awesome. I thought there must be a better way

Thanks
E