Filling the empty production date

Question

Dear Masters!&nbsp;I need your help. I am spending more than two days for one formula.&nbsp;Table&nbsp;Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Product&nbsp; &nbsp; Process&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Q'ty2022.5.1&nbsp; &nbsp; &nbsp; &nbsp;Shoes&nbsp; &nbsp; &nbsp; &nbsp;Right Bottom&nbsp; &nbsp; &nbsp;12022.5.3&nbsp; &nbsp; &nbsp; &nbsp;Shoes&nbsp; &nbsp; &nbsp; &nbsp;Left Bottom&nbsp; &nbsp; &nbsp; &nbsp; 12022.5.6&nbsp; &nbsp; &nbsp; &nbsp;Shoes&nbsp; &nbsp; &nbsp; &nbsp;Right Top&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12022.5.9&nbsp; &nbsp; &nbsp; &nbsp;Shoes&nbsp; &nbsp; &nbsp; &nbsp;Completed&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp;&nbsp;I want to make a graph with the quantity of work in processes.&nbsp;So I need to make a table like this.&nbsp;Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Product&nbsp; &nbsp; &nbsp; &nbsp; Q'ty2022.5.1&nbsp; &nbsp; &nbsp; &nbsp; Shoes&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12022.5.2&nbsp; &nbsp; &nbsp; &nbsp; Shoes&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12022.5.3&nbsp; &nbsp; &nbsp; &nbsp; Shoes&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12022.5.4&nbsp; &nbsp; &nbsp; &nbsp; Shoes&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12022.5.5&nbsp; &nbsp; &nbsp; &nbsp; Shoes&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12022.5.6&nbsp; &nbsp; &nbsp; &nbsp; Shoes&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12022.5.7&nbsp; &nbsp; &nbsp; &nbsp; Shoes&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12022.5.8&nbsp; &nbsp; &nbsp; &nbsp; Shoes&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12022.5.9&nbsp; &nbsp; &nbsp; &nbsp; Shoes&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12022.5.102022.5.112022.5.12&nbsp;This means I don't want any quantity after the appointed date like the completed day.I have tried to use the lastnonblankvalue, but it is showing the quantity until the max date in the Date[Date].If I use the date in the process table, the calculation takes a super long time.&nbsp;Is there any wonder master can help me?

anonymous · Accepted Answer

HI&nbsp;Young_G_Han,
You can summarize raw table records and use crossjoin with a calendar date to create the expanding table, then you can use raw table field values to filter the result table records which are not included in the ranges.
CROSSJOIN function (DAX) - DAX | Microsoft Docs
NewTable =
SELECTCOLUMNS (
    FILTER (
        CROSSJOIN (
            SUMMARIZE (
                Table,
                [Product],
                "Start", MIN ( Table[Date] ),
                "End", MAX ( Table[Date] )
            ),
            Calendar
        ),
        [Date] &gt;= [Start]
            &amp;&amp; [Date] &lt;= [End]
    ),
    "Date", [Date],
    "Product", [Product],
    "Qty", 1
)
Regards,
Xiaoxin Sheng

Forum Discussion

Filling the empty production date

2 Replies

Related content

Filled map

filling empty cells with 0

Production Lessons

Fill empty cells based on criteria

Azure Maps filled map layer – empty regions show default color despite "Don't format" setting