Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Incorrect Line chart values for blank months.

I have a cumulative line chart that displays list of open requisitions.  The cumulative values displays correctly; however when I try to filter on Manager or Org, there are cases where some months do not have any data.  The line chart ends up displaying the "final" cumulative value from "Current Year" instead of taking the value from the previous month.  In the example below, Feb 2019 does not have any Opened or Filled data.  I would expect the line chart to continue to Feb 2019 with 15.  Instead it jumps to 35 (final cumulative value of Current Year). The same issue occurs in Oct 2019.  What is the best way to fill this gap.

 

Line Measure: CALCULATE(SUM('Opened Reqs'[OPEN_BY_MONTH]) + sum('Filled Reqs'[FILL_CLOSE_BY_MONTH]),
CALCULATETABLE(DATESBETWEEN('Forecast Calendar'[Date],[Min Opened Date],[Max Date])))

 

Min Date: If ([Min Open Date] < [Min Filled Date],[Min Open Date],[Min Filled Date])

Max: Date: If ([Max Open Date]> [Max Filled Date],[Max Open Date],[Max Filled Date])

 

The graph below has "Last Year" selected.

 

 

 

Need Help

1 Reply

  • kentyler's avatar
    kentyler
    Solution Sage
    6 years ago

    It looks like when there is not current month the IFs select the MIN and MAX of all the data 

    Min Date: If ([Min Open Date] < [Min Filled Date],[Min Open Date],[Min Filled Date])

    Max: Date: If ([Max Open Date]> [Max Filled Date],[Max Open Date],[Max Filled Date])
    You may have to add an IF(ISBLANK([Min Open Date])... or something like that to detect that the current month is empty and have it then do a different calculation.