Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Help with cumulative line chart with difference per month

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.

 

Need Help

2 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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.