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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Have you tried creating a running total column or measure?

 

Make sure you have a column 'MonthNumber' which corresponds to the number of the month (1 for Jan, 2 for Feb etc.)

 

Items running total = 
CALCULATE(
    SUM('Table'[ItemCount]),
    FILTER(
        ALLSELECTED('Table'[MonthNumber]),
        ISONORAFTER('Table'[MonthNumber], MAX('Table'[MonthNumber]), DESC)
    )
)
Sixian's avatar
Sixian
Frequent Visitor
7 years ago

Sorry.

What do you mean for 

'Table'[ItemCount]

? 

 

We can suppose we just have a table with the three columns: |item|target date|actual date|

The 'Table' should be this table with all the items and dates, but what column is [ItemCount]?