Creating Cumulative Graphs

[mick_lowe]

Hi All

I've looked through the posts and although similar questions have been asked I can't find one that matches what I'm trying to do but sorry if this has been asked in a different way.

I want to create a visual showing 2 different line graphs pulling data from 2 different tables, I can get them working individually and I suspect the problem is down to the "Date" fields.

My data looks like this:

One table is for time spent working on a job, the other is invoices against that job and as you can see the dates don't match.

If I create a graph using NDate from table A the time graph looks to be correct but when I ad the billing totals I just get a straight line across the graph:

I'm guessing this is because its trying to plot the invoice amount against the time worked date, is there any way to get this to work or do I need to go back to 2 seperate visuals?

Thanks

Mick

[v-kathullac]

Thankyou @rajendraongole1 , @johnt75 , @danextian  for Addressing the issue.

Hi  @mick_lowe  ,

Thank you for reaching out to Microsoft Fabric Community Forum,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

Regards,

Chaithanya

[mick_lowe]

Hi @v-kathullac

I haven't got this working but can see from see from the post by @danextian how it should work so I've marked as the solution.

Thanks

Mick

[v-kathullac]

Hi @mick_lowe ,

Thank you for reaching out to Microsoft Fabric Community Forum,

• The query fails with the error: "The query has exceeded the available resources." The error indicates that the query exceeded the available memory, CPU, or capacity limits.
• The issue may be related to Large dataset or high data volume, Complex DAX measures or Power Query transformations, Inefficient data model or expensive query execution,Power BI/Fabric capacity or resource limitations.

Kindly check the above debug steps to resolve the Exceed issue.Let us know if you need any further assistance?

Thanks,

Chaithanya.

[v-kathullac]

Thankyou @rajendraongole1 , @johnt75  for Addressing the issue.

Hi  @mick_lowe  ,

Thank you for reaching out to Microsoft Fabric Community Forum,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

Regards,

Chaithanya

[mick_lowe]

Hi @rajendraongole1 @johnt75 @v-kathullac 

Thanks for all the replies, this is a bit of a "pet project" and not my full time job, so sorry for not coming back to you sooner.

I couldn't get this to work using the suggetions, what I ended up doing was creating 2 fields on each table:

Total Time Spent =
SUM('zprojects'[timespent])

And:

Cumulative Time Spent =
CALCULATE(
    [Total Time Spent],
    FILTER(
        ALL('Calendar'[Date]),
        'Calendar'[Date] <= MAX('Calendar'[Date])
    )
)

This works for some:

but not others so I'm currently trying to work out why:

[danextian · Accepted solution]

Did you use the date column from the calendar table in your visual and relationship is one to many single direction from the dim to fact tables? Also, since you want the cumulative (from the very first date), apply FILTER to the whole table instead of to just a specific column.

Please see the attached pbix. 

[mick_lowe]

Thanks Dane, I tried this but this returns "query has exceeded the available resources".

[johnt75]

Create a date table and mark it as a date table. There are lots of videos or tools you can use to do this.

Link your date table to both of your existing tables and use columns from the date table on your charts.

[mick_lowe]

Thanks @johnt75 do I need to move the measure to that table for this to work?

Mick

[rajendraongole1]

Hi @mick_lowe  - No, the measure doesn't need to be moved. A measure can live in any table ,

The key thing is to use the Date column from the Calendar table on the visual and ensure both fact tables have active relationships to it. Moving measures to a dedicated Measures table is purely for organization and has no impact on the result.

Hope this helps.

[johnt75]

This is correct about where measures can live but I would disagree about having to use the Date column of the Calendar table on the visual.

The Date column is used for the relationships, but any column from the Calendar table can be used for visuals, e.g. Year Month. The column used will likely change from visual to visual, depending on the granularity that you want to show.

[rajendraongole1]

Hi @johnt75 - Good point- thanks for the clarification. I should have said to use a column from the Calendar table rather than specifically the Date column.

The appropriate column depends on the level of detail you want to display (e.g., Date, Year Month, Quarter, or Year). The key is that the visual uses the Calendar table so both fact tables are filtered consistently through the date relationships.

I hope this clarifies.