topic Re: Add missing dates with 0 in DAX in DAX Commands and Tips

Add missing dates with 0 in DAX

liselotte — Thu, 13 Jun 2024 15:47:31 GMT

I extract the original table 'org_table' with the following query

table = SELECTCOLUMNS( FILTER( 'org_table', 'org_table'[Type] = "H" && 'org_table'[Date] < TODAY() ), "Place", 'org_table'[Place], "Factory", 'org_table'[Factory], "Device", 'org_table'[Device], "Value", 'org_table'[Value], "Date", 'org_table'[Date] )

which resulted in this table

How can I change the query so that the resulted table are added missing dates from 01/01/2024 to today with Value=0 for each combination of Place, Factory and Device? Because this is just the simplified version of my problem, I would like to do it in DAX so that I can it in my real query. Thank you.

Re: Add missing dates with 0 in DAX

Anonymous — Fri, 14 Jun 2024 09:20:20 GMT

Hi liselotte ,

How do you make sense of adding the results table with missing dates from January 1, 2024 to today and Value=0 for each combination of locations, plants, and equipment?

Best Regards,
Adamk Kong

Re: Add missing dates with 0 in DAX

liselotte — Fri, 14 Jun 2024 12:36:17 GMT

Hi Anonymous , thank you for replying. I mean I want to get a table resulted from LEFT JOIN on columns ['Date'] of Table CALENDAR(DATE(2023,01,01), TODAY()) and the table in the provided query, I know I can do it with Power Query, but for my real problem, I would like to do it in DAX, which I need help with.