Summarize Average Date by Batch Number and Movement Type

[Anonymous]

I would like to summarize the batch numbers by movement type.  For example, Batch 3T36 has multiple entries.  They all have the same movement type but different dates.   I would like to take the average of the 'Posting Date' for Movement Type 901 for the Batch number.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Based on the information, try using the following DAX formula to calculate the date.

Measure1 = 
VAR FirstPosting =
    CALCULATE(
        FIRSTDATE(TEST[Posting Date]),
        TEST[Movement Type] = "901",
        TEST[Plant] = "FRPB"
    )
VAR LastPosting =
    CALCULATE(
        LASTDATE(TEST[Posting Date]),
        TEST[Movement Type] = "901",
        TEST[Plant] = "FRPB"
    )
VAR MidPosting =
    FirstPosting
        + INT(DATEDIFF( FirstPosting, LastPosting, DAY ) / 2)
RETURN
    MidPosting

Measure2 = 
VAR FirstPosting =
    CALCULATE(
        FIRSTDATE(TEST[Posting Date]),
        TEST[Movement Type] = "68C",
        TEST[Plant] = "FRAA"
    )
VAR LastPosting =
    CALCULATE(
        LASTDATE(TEST[Posting Date]),
        TEST[Movement Type] = "68C",
        TEST[Plant] = "FRAA"
    )
VAR _date =
    FirstPosting
        + INT(DATEDIFF( FirstPosting, LastPosting, DAY ) / 2)
RETURN
    _date

Measure days = DATEDIFF([Measure1], [Measure2], DAY)

Then, drag the measure to the card visual.

Best Regards,

Wisdom Wu

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

[parry2k]

@Anonymous sorry not clear what is the expected output, could you please clarify that? 

[Anonymous]

Hi,   I would like to have a measure using 

Add_Date = FIRSTDATE(Sheet1[Posting Date]) + INT(DATEDIFF(FIRSTDATE(Sheet1[Posting Date]),LASTDATE(Sheet1[Posting Date]),DAY) / 2) that filters by movement type and Plant embedded within the DAX.   Then I would like to subtract this measure from another measure to get how long this batch stayed before moving out.     For example:   

Measure 1= average date for batch # with movement type 901 at Factory FRPB –  Measure 2 = average date for batch number with movement type 68C at FRAA.