Forum Discussion

liselotte's avatar
liselotte
Icon for Advocate I rankAdvocate I
2 years ago

Add missing dates with 0 in DAX

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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    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

    • liselotte's avatar
      liselotte
      Icon for Advocate I rankAdvocate I

      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.