Forum Discussion

Sixian's avatar
Sixian
Frequent Visitor
7 years ago
Solved

Cumulative double line chart operating on dates

Hi, I have a table with a list of items with a "target date" and an "actual date" (or blank) associated. As you can see from the attached image, I use a slicer to filter the visuals by the year of ...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Sixian,

     

    Based on the Anonymous's solution, you can try this one.

     

    Items running total =
CALCULATE (
    COUNT ( 'Table'[Item] ),
    FILTER (
        ALLSELECTED ( 'Table'[MonthNumber] ),
        ISONORAFTER ( 'Table'[MonthNumber], MAX ( 'Table'[MonthNumber] ), DESC )
    )
)

    I would suggest you create a date table if you don't have one.

     

     

    Measure =
CALCULATE (
    COUNT ( 'table'[item] ),
    FILTER (
        ALLSELECTED ( 'datetable' ),
        'datetable'[date] <= MAX ( 'datetable'[date] )
    )
)

    Or please share a sample file? Mask the sensitive parts first.

     

     

    Best Regards,
    Dale

Sixian's avatar
Sixian
Frequent Visitor
7 years ago
I tried to understand and apply the suggestion, but the chart shows nothing.
Any help would be greatly appreciated.
  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Sixian,

     

    Based on the Anonymous's solution, you can try this one.

     

    Items running total =
CALCULATE (
    COUNT ( 'Table'[Item] ),
    FILTER (
        ALLSELECTED ( 'Table'[MonthNumber] ),
        ISONORAFTER ( 'Table'[MonthNumber], MAX ( 'Table'[MonthNumber] ), DESC )
    )
)

    I would suggest you create a date table if you don't have one.

     

     

    Measure =
CALCULATE (
    COUNT ( 'table'[item] ),
    FILTER (
        ALLSELECTED ( 'datetable' ),
        'datetable'[date] <= MAX ( 'datetable'[date] )
    )
)

    Or please share a sample file? Mask the sensitive parts first.

     

     

    Best Regards,
    Dale

    • Sixian's avatar
      Sixian
      Frequent Visitor
      7 years ago

      I'm now using the date table and following the new instructions. I'm getting close, but the numbers are still not exact. May be there is something in the filters. I'll let you know.

      Thanks for now.