Help with cumulative line chart with difference per month

[Anonymous]

I need help plotting a line chart over Month Year that gives me the current total based on open and closed positions.  For some reason the TOTAL_OPEN (line chart) at the end of each month displays correct unitl May 2019 but not for subsequent month.  Ex: June should should be (1134 + 222 -240) = 1116 instead It computes to 1390.  July should be (1116+275-295) = 1096.

[MFelix]

Hi @Anonymous ,

You need to create a measure based on the open and close dates then add the following measure:

Current Result =
CALCULATE (
    SUM ( 'Table'[Open by month] ) + SUM ( 'Table'[Closed by month] );
    FILTER (
        ALLSELECTED ( 'Table'[End of month] );
        'Table'[End of month] <= MAX ( 'Table'[End of month] )
    )
)

If you are using a calendar table the End of month column should be replaced by your calendar column.

Check PBIX file attach.

[Anonymous]

Hi @MFelix,

The calc worked but I ran into an issue.  I have a slicer (Group, BU, Division) and the data didn't toggle when I clicked the options in the slicer.  I then added the Group, BU, Division and Deptid (which is the key), it flatlineed the data instead of giving a increasing total.  I have alll the mappings joined correctly, maybe I'm missing a filter.  My line should be more like the red line.  Again your calc worked fine and I got the correct data prior to addding the Group, BU, Division and Deptid but I need the line to work with those new columns.