cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sixian
Frequent Visitor

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 the target dates. This way I get a chart with a bar showing the total number of items (a COUNT) with a target date in the selected year and a bar showing the total number of items with an actual date in the selected year. The second chart shows the same information, but drilled to each single month of the year.

 

 ScreenShot 001.png

 

I need to add a double line chart showing the same values of the second chart (the monthly chart), but with cumulative values (COUNT). So, for instance, the February "target date" value will be the count of all the items with a target date included in both January and February and so on.

 

I couldn't find a working solution by looking to other posts, so I ask for your help.

 

Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-jiascu-msft
Microsoft

Re: Cumulative double line chart operating on dates

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Re: Cumulative double line chart operating on dates

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
Frequent Visitor

Re: Cumulative double line chart operating on dates

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]?

Highlighted
Sixian
Frequent Visitor

Re: Cumulative double line chart operating on dates

I tried to understand and apply the suggestion, but the chart shows nothing.
Any help would be greatly appreciated.
Microsoft v-jiascu-msft
Microsoft

Re: Cumulative double line chart operating on dates

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Sixian
Frequent Visitor

Re: Cumulative double line chart operating on dates

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.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors