Forum Discussion

Young_G_Han's avatar
Young_G_Han
Icon for Helper III rankHelper III
4 years ago
Solved

Filling the empty production date

Dear Masters!   I need your help. I am spending more than two days for one formula.   Table   Date             Product    Process            Q'ty 2022.5.1       Shoes       Right Bottom     1 ...
  • Anonymous's avatar
    Anonymous
    4 years ago

    HI 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] >= [Start]
                && [Date] <= [End]
        ),
        "Date", [Date],
        "Product", [Product],
        "Qty", 1
    )

    Regards,

    Xiaoxin Sheng