Measure over Time Graph Display Issues

[M_Frascella]

Hi all,

I'm attempting to graph a measure over time, but I'm struggling to get it to display as desired.

My measure shows me the sum of the part costs at a point in time of all of the parts within the filter context for their most recent part plan as shown below.

Supporting measure:

Part Cost Filtered =
VAR maxDate =
    MAX ( 'Part Plan'[EFFECTIVITY_DATE] )
VAR maxPlanEffDate =
    CALCULATE (
        MAX ( 'Part Plan'[EFFECTIVITY_DATE] ),
        'Part Plan'[EFFECTIVITY_DATE] <= maxDate
    )
VAR partCost =
    CALCULATE (
        MAX ( 'Part Plan'[PART_COST] ),
        REMOVEFILTERS ( 'Part Plan'[EFFECTIVITY_DATE] ),
        'Part Plan'[EFFECTIVITY_DATE] = maxPlanEffDate
    )
RETURN
    partCost

Measure being used in graph:

Sum Active Part Cost =
VAR partCostTable =
    ADDCOLUMNS (
        SUMMARIZE ( 'Part Plan', 'Part Plan'[PART_NBR] ),
        "PartCost", 'Measure Table'[Part Cost Filtered]
    )
RETURN
    SUMX ( partCostTable, [PartCost] )

 I want to show the value of this measure at a point in time in context of PROJECT_IDs and EFFECTIVITY_DATE, which would be my time axis for a line graph. When I put this value into a card and use an effectivity date filter that is on or before x date, I get the expected result. However, when I put it in a line graph to show it over time, it only sums up part plans that have an effectivity date of the selected day. I understand why this happens, but I need a way for the graph to instead filter as "on or before selected date" rather than filtering on "is selected date" either by updating the measures or updating the graph.

Any help would be greatly appreciated. I've included below information that I think will be necessary or otherwise useful, but I can provide more if necessary.

Part Plan Table

PART_NBR	EFFECTIVITY_DATE	PART_COST	PROJECT_ID
A	1/17/2022	1320.94	3
B	1/17/2022	1320.94	3
A	1/18/2022	1100.78	3
B	1/18/2022	1100.78	3
B	2/19/2022	785.99	3
A	3/1/2022	785.99	3
A	3/18/2023	599.37	3
B	3/18/2023	599.37	3

Summarized Data Table

Note: The red italicized values do not appear in the data source (as there is no part plan that has that effectivity date for that part number), but shows what the value is at that point in time.

	A Part Cost	B Part Cost	Expected Output
1/17/2022	1320.94	1320.94	2641.88
1/18/2022	1100.78	1100.78	2201.56
2/19/2022	1100.78	785.99	1886.77
3/1/2022	785.99	785.99	1571.98
3/18/2023	599.37	599.37	1198.74

Current graph output:

Desired graph output:

[lbendlin]

This part

VAR maxDate =
    MAX ( 'Part Plan'[EFFECTIVITY_DATE] )
VAR maxPlanEffDate =
    CALCULATE (
        MAX ( 'Part Plan'[EFFECTIVITY_DATE] ),
        'Part Plan'[EFFECTIVITY_DATE] <= maxDate
    )

doesn't seem to make sense.  It wil return the same date again.  Maybe you can show with an example how you are planning to arrive at the number for Feb 19 2022

[M_Frascella]

Sure! My thinking behind doing this is that in the final measure, it will be summarized by part number and the graph will further summarize it by effectivity date and project (I accidentally left this out of the original post, but it is added now). However, if I leave it to look for just the part plan that has an effectivity date equal to the max effectivity date, if the part number doesn't have a part plan that went effective on that exact date, it won't appear. However, I still need to account for it as there is still an active plan for that part number under the given project. For that part number, it needs to look at the plan with max effectivity date that is less than or equal to the selected effectivity date under the given project (which would be the active plan).

Using the example of Feb 19 2022, on this date under PROJECT_ID 3, part numbers A and B are both a part of PROJECT_ID 3, so I need the sum of the part costs of their active plans as of that date. B is the part number that changed on this date, so it has a max effectivity date of Feb 19 2022. A didn't change, but still has an active part plan under this project, so the max effectivity date for this should be Jan 18 2022. With these effectivity date values, it should find the correct part cost values. 

Below is how it works when I have the effectivity date filter set as "is on or before", which is how I'd like the calculation to be done even in the final version.

Note: Part Cost Filtered and Sum Active Part Cost are the same measures as above. Max Effective Date is as shown:

Max Effective Date = 
VAR maxDate =
    MAX ( 'Part Plan'[EFFECTIVITY_DATE] )
VAR maxPlanEffDate =
CALCULATE (
    MAX ( 'Part Plan'[EFFECTIVITY_DATE] ),
    'Part Plan'[EFFECTIVITY_DATE] <= maxDate
)

RETURN maxPlanEffDate