Dynamic Date Table - When Fact table is empty

Power Query
1

Good morning,
I have an issue with my dynamic Date Table.
We have new clients that we need to onboard include our Power Bi tool.
How it is set it up, we look at our Fact Table and pick up the first and last invoice in the table.

The situation is, those clients start with an empty “Fact table”, so the queries cannot find first / last invoice.
Could you help me to write a line where if the date is null, bring todays date, so the date table is populated, and power BI can run?

DnaDatesTable
DnaDatesTable759×219 19.9 KB

Current is:
= list.Min(DsPayableInvoice[TransactionDate])

I need somthing lik
= if ( (list.Min(DsPayableInvoice[TransactionDate])) = null,
Date.From(DateTime.LocalNow()),
List.Min(DsPayableInvoice[TransactionDate])
)

… I had a go, but it doesn’t work…

Any other way is also welcome!

Thanks,

2

@ezenunez ,

It’s always tough to try to provide a specific solution without a PBIX to test on, but one thing I noticed off the bat is that your IF statement follows the DAX format, and the M format is entirely different. Give this a go:

if list.Min(DsPayableInvoice[TransactionDate]) = null then
Date.From(DateTime.LocalNow()) else
List.Min(DsPayableInvoice[TransactionDate])

  • Brian
3

Thanks Brian,
I does work if you add the today date at the end !
= List.Min(DsPayableInvoice[TransactionDate],Date.From(DateTime.LocalNow()) )

I look forward to get the power query tutoria!
Cheers,
Ezequiel

1 Like
4

@ezenunez,

Great – glad that got you what you needed.

The course is fantastic - I think you’re all going to love it. 10 AM Eastern time rollout, Wednesday 6/30.

  • Brian