Forum Discussion

Bukha's avatar
Bukha
Frequent Visitor
8 years ago
Solved

Adding rows for unrecorded days

Greetings all,

 

 

 

I have a table that has some data based on days of the month like the below example:


 

How is it possible to add the dates between the shown dates and assign them to zero in Dax?

  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi Bukha,

     

    Seems you'd like a new table. Try the formula below please.

    Table =
ADDCOLUMNS (
    CALENDAR ( MIN ( 'Table1'[Date] ), MAX ( 'Table1'[Date] ) ),
    "# of Porducts", IF (
        ISBLANK ( LOOKUPVALUE ( Table1[# of Products], Table1[Date], [Date] ) ),
        0,
        LOOKUPVALUE ( Table1[# of Products], Table1[Date], [Date] )
    )
)

    Adding_rows

     

    Best Regards,

    Dale

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    I would recommend to create a Date table with all of the continuous dates for the time period that you are working with. Then you can use the Date field from this newly-created table in your visualizations. If you manage your filter directions, you can acheive what you're asking.

     

    Hope this helps,

    Parker

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi Bukha,

     

    Seems you'd like a new table. Try the formula below please.

    Table =
ADDCOLUMNS (
    CALENDAR ( MIN ( 'Table1'[Date] ), MAX ( 'Table1'[Date] ) ),
    "# of Porducts", IF (
        ISBLANK ( LOOKUPVALUE ( Table1[# of Products], Table1[Date], [Date] ) ),
        0,
        LOOKUPVALUE ( Table1[# of Products], Table1[Date], [Date] )
    )
)

    Adding_rows

     

    Best Regards,

    Dale