Relationship between date table and data table (with null date)

[Anonymous]

Hi guys,

I have 2 tables INVOICE and DATE

Date table =

 ADDCOLUMNS(

calendar(min(INVOICE[INVOICE DATE]), EOMONTH(TODAY(),-1)), 

"MonthNum", MONTH([Date]),
"Month", FORMAT([Date], "MMMM"),

"Year", YEAR([Date])
)

there is a relationship between INVOICE and DATE (* ...1) by the field 'INVOICE'[INVOICE DATE] and 'DATE'[Date]

for a unknow reason, [INVOICE DATE] can have value NULL. Let's admit it's possible.

I always used my 'Date' table in filters, formulas, etc

I have some question :

1) Do you think what I have done is correct ?

2) when I put the 'Date'[Date] in the page filter, why there is null value (VIDE) in the list. There is not null value in my date table

3)I made a table with the hierarchy of the date table and the 'INVOICE'[N° Invoice] to count the number distinct of invoice, there is 49673 for date = null

but when I checked in the data tab and I filtered 'INVOICE'[Invoice Date] = null, there is only  6469 line. There is a inconsistency

Can you tell me what I did wrong ?

Thank you

[v-stephen-msft]

Hi @Anonymous ,

The following articles shows you how to remove blank from slicers:

https://www.c-sharpcorner.com/article/how-to-remove-blank-from-power-bi-slicer/

https://www.encorebusiness.com/blog/blank-value-in-the-power-bi-slicer-and-filter/

Best Regards,

Stephen Tao

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

[amitchandak]

@Anonymous , I think there are missing date, try like

ADDCOLUMNS(

calendar(min(INVOICE[INVOICE DATE]), EOMONTH(Max(INVOICE[INVOICE DATE]),0)), 

"MonthNum", MONTH([Date]),
"Month", FORMAT([Date], "MMMM"),

"Year", YEAR([Date])
)