Historical and Current Cumulative Open Work Order Value

[cspande]

I'm looking to write a measure to sum work order values based on when they were open in any given day while also providing the current total open work order value. The work order table has a create_date and a closed_date. My date table and work order table are in a relationship via create_date. 

I believe I am correctly calculating the historical open work order value with the following measure:

VAR CurrentMonthStart = MIN('Date'[Date])
VAR CurrentMonthEnd = MAX('Date'[Date])
VAR HistoricalWIP = 
    CALCULATE(
        SUM(APPEND_WORK_ORDER[TOTAL_ACTUAL_COST]),
        APPEND_WORK_ORDER[CREATE_DATE] <= CurrentMonthEnd &&
        (ISBLANK(APPEND_WORK_ORDER[TRENDING_DATE]) || APPEND_WORK_ORDER[TRENDING_DATE] >= CurrentMonthStart))
RETURN
HisotricalWIP

I've been struggling with how to represent the cumulative current open work order value on todays date.

Historical Values:

Current Open Work Order Value:

Ideally, the Jan 2025 monthly value would equal the scorecard above. Is this possible? Each day will have new open work orders as we continuous release work to the production facilities.

Thanks,
Craig

[cspande]

Here is sample data of work order values by create date and close date:

create_date	close_date	total_actual_cost
1/8/2024	1/29/2024	23
1/14/2024	2/17/2024	43
1/20/2024	3/7/2024	42
1/26/2024	3/26/2024	7
2/1/2024	4/14/2024	83
2/7/2024	2/10/2024	13
2/13/2024	5/22/2024	65
2/19/2024	6/10/2024	53
2/25/2024	6/29/2024	54
3/2/2024	7/18/2024	56
3/8/2024	8/6/2024	94
3/14/2024	8/25/2024	13
3/15/2024		70
4/1/2024	10/21/2024	2
4/7/2024	11/9/2024	75
5/1/2024	6/17/2024	72
5/31/2024	9/30/2024	5
6/18/2024	12/2/2024	47
6/24/2024	12/23/2024	59
7/6/2024	7/10/2024	37
8/5/2024	1/8/2024	53
9/4/2024	12/2/2024	2
9/28/2024	12/6/2024	69
10/4/2024	12/7/2024	57
10/28/2024	12/11/2024	80
11/3/2024	12/12/2024	41
11/9/2024	12/13/2024	9
11/15/2024		58
11/21/2024		96
11/27/2024		62
12/3/2024		74
12/9/2024		91
12/15/2024		29
12/21/2024		49
12/27/2024		51
1/15/2025		56
1/27/2025		58
2/1/2025	2/3/2025	61

Here is my expected result:

Date	Open Value	Month-Year
1/31/2024	92.00	Jan-24
2/29/2024	304.00	Feb-24
3/31/2024	488.00	Mar-24
4/30/2024	482.00	Apr-24
5/31/2024	494.00	May-24
6/30/2024	421.00	Jun-24
7/31/2024	365.00	Jul-24
8/31/2024	311.00	Aug-24
9/30/2024	382.00	Sept-24
10/31/2024	512.00	Oct-24
11/30/2024	703.00	Nov-24
12/31/2024	633.00	Dec-24
1/31/2025	694.00	Jan-25
2/3/2025	755.00	Feb-25

[lbendlin · Accepted solution]

I'm getting slightly diffferent results

[cspande]

You are correct in the calculation. My addition was incorrect when I manually entered the data. Thank you!

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly.

I tried to create a sample pbix file like below, and please check the below picture and the attached pbix file.

Cost by creat date: = 
SUM(append_work_order[total_actual_cost])

Cost: = 
VAR _start =
    MIN ( 'calendar'[Date] )
VAR _end =
    MAX ( 'calendar'[Date] )
VAR _t =
    FILTER (
        ALL ( append_work_order ),
        _end >= append_work_order[create_date]
            && OR (
                _start <= append_work_order[trending_date],
                ISBLANK ( append_work_order[trending_date] )
            )
    )
RETURN
    SUMX ( _t, append_work_order[total_actual_cost] )

WINDOW function (DAX) - DAX | Microsoft Learn

Cost cumulative: = 
VAR _window =
    WINDOW (
        1,
        ABS,
        0,
        REL,
        ALL ( 'calendar'[Month-Year], 'calendar'[Month-Year sort] ),
        ORDERBY ( 'calendar'[Month-Year sort], ASC )
    )
RETURN
    CALCULATE ( [Cost:], _window )

[Anonymous]

Hi @cspande 

Did the solutions @Jihwan_Kim   offered help you solve the problem, if it helps, you can consider to accept it as a solution so that more user can refer to.

Best Regards!

Yolo Zhu

[cspande]

This did not work as a solution for me.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...