To get the date of production on when it achieves sqft planned

[New_be]

Hi, I have an issue to create things that I wanted in the query editor (M query). I wanted to get the date of production when that orderID per process achieve sqft planned. I tried few things, but it didn’t work. I attached an excel file of the detail (small and simple details) that I wanted for your reference. Thank you for the help in advance.

[danextian · Accepted solution]

Hi @New_be 

Doing it in Power Query would be slow as it would involve grouping rows and scaning a table.  DAX is more optimized for that.  As measures, create these:

Percent 95 = 570 //can be an expression like DIVIDE ( numerator, denominator )

Date01 = 
VAR _TBL =
    ADDCOLUMNS(
        SUMMARIZE('Table', 'Table'[OrderID], 'Table'[ProcessID], 'Table'[Date]),
        "@Cumulative",
        CALCULATE(
            SUM('Table'[SQFT]),
            FILTER(
                ALLEXCEPT('Table', 'Table'[OrderID], 'Table'[ProcessID]),
                'Table'[Date] <= EARLIER('Table'[Date])
            )
        )
    )
VAR _Filtered =
    FILTER(_TBL, [@Cumulative] >= [Percent 95])
VAR _MinDate =
    MINX(_Filtered, [Date])
RETURN
_MinDate


Date02 = 
VAR _Date =
    CALCULATE (
        [Date01],
        ALLEXCEPT ( 'Table', 'Table'[OrderID], 'Table'[ProcessID] )
    )
RETURN
    IF ( SELECTEDVALUE ( 'Table'[Date] ) = _Date, _Date )

As a calculated table, make sure [Percent 95] measure exists and then create this:

CalcTbl = 
VAR _TBL =
    ADDCOLUMNS (
        SUMMARIZE ( 'Table', 'Table'[OrderID], 'Table'[ProcessID], 'Table'[Date] ),
        "@Cumulative",
            CALCULATE (
                SUM ( 'Table'[SQFT] ),
                FILTER (
                    ALLEXCEPT ( 'Table', 'Table'[OrderID], 'Table'[ProcessID] ),
                    'Table'[Date] <= EARLIER ( 'Table'[Date] )
                )
            )
    )
VAR _Filtered =
    FILTER ( _TBL, [@Cumulative] >= [Percent 95] )
RETURN
    GROUPBY (
        _Filtered,
        'Table'[OrderID],
        'Table'[ProcessID],
        "Date", MINX ( CURRENTGROUP (), [Date] )
    )

Please see attached sample pbix

[New_be]

Thank you guys! it helps alot + I learn new thing. Thanks again. 🙂

[New_be]

I forgot to attached link in the request. So below is the link:
Pbix example 

[Anonymous · Accepted solution]

Hi @New_be ,

According to your sample, I think there are two tables in your data model: PlannedTable and OutputTable.

Add a [95% from SQFT_PLANNED] in PlannedTable:

OutputTable:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjcwtFDSUXLz9AOShgb6hqb6RgZGJiCOobFSrA4WFWYwFSam5thVmMNUGBsgKwgI8ocqsIApMDICGhELAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OrderID = _t, ProcessID = _t, Date = _t, SQFT = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"OrderID", Int64.Type}, {"ProcessID", type text}, {"Date", type date}, {"SQFT", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"OrderID"}, PlannedTable, {"OrderID"}, "PlannedTable", JoinKind.LeftOuter),
    #"Expanded PlannedTable" = Table.ExpandTableColumn(#"Merged Queries", "PlannedTable", {"95% from SQFT_PLANNED"}, {"95% from SQFT_PLANNED"}),
    #"Added CumulativeSqft" = Table.AddColumn(#"Expanded PlannedTable", "CumulativeSqft", each let 
    _OrderID = [OrderID],
    _Date = [Date]
    in
    List.Sum(
    Table.SelectRows(#"Expanded PlannedTable", each [OrderID] = _OrderID and [Date]<=_Date )[SQFT])),
    
    #"Added DateAchieveTarget" = Table.AddColumn(#"Added CumulativeSqft", "DateAchieveTarget", each let 
    _OrderID = [OrderID]
    in
    if [CumulativeSqft] = 
    List.Min(Table.SelectRows(#"Added CumulativeSqft", each [OrderID] = _OrderID and [CumulativeSqft]>=[#"95% from SQFT_PLANNED"])[CumulativeSqft])
    then [Date] else null)
in
    #"Added DateAchieveTarget"

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danextian · Accepted solution]

Hi @New_be 

Doing it in Power Query would be slow as it would involve grouping rows and scaning a table.  DAX is more optimized for that.  As measures, create these:

Percent 95 = 570 //can be an expression like DIVIDE ( numerator, denominator )

Date01 = 
VAR _TBL =
    ADDCOLUMNS(
        SUMMARIZE('Table', 'Table'[OrderID], 'Table'[ProcessID], 'Table'[Date]),
        "@Cumulative",
        CALCULATE(
            SUM('Table'[SQFT]),
            FILTER(
                ALLEXCEPT('Table', 'Table'[OrderID], 'Table'[ProcessID]),
                'Table'[Date] <= EARLIER('Table'[Date])
            )
        )
    )
VAR _Filtered =
    FILTER(_TBL, [@Cumulative] >= [Percent 95])
VAR _MinDate =
    MINX(_Filtered, [Date])
RETURN
_MinDate


Date02 = 
VAR _Date =
    CALCULATE (
        [Date01],
        ALLEXCEPT ( 'Table', 'Table'[OrderID], 'Table'[ProcessID] )
    )
RETURN
    IF ( SELECTEDVALUE ( 'Table'[Date] ) = _Date, _Date )

As a calculated table, make sure [Percent 95] measure exists and then create this:

CalcTbl = 
VAR _TBL =
    ADDCOLUMNS (
        SUMMARIZE ( 'Table', 'Table'[OrderID], 'Table'[ProcessID], 'Table'[Date] ),
        "@Cumulative",
            CALCULATE (
                SUM ( 'Table'[SQFT] ),
                FILTER (
                    ALLEXCEPT ( 'Table', 'Table'[OrderID], 'Table'[ProcessID] ),
                    'Table'[Date] <= EARLIER ( 'Table'[Date] )
                )
            )
    )
VAR _Filtered =
    FILTER ( _TBL, [@Cumulative] >= [Percent 95] )
RETURN
    GROUPBY (
        _Filtered,
        'Table'[OrderID],
        'Table'[ProcessID],
        "Date", MINX ( CURRENTGROUP (), [Date] )
    )

Please see attached sample pbix