cumulative column in a bar chart created by month

[PaulusD]

Hi all,

looking for some advice here.

I have a bar chart that shows numerous values by month (year - month index, included in a dax date table).

For the current month i trying to add a cumulative total for one of the values, unfortunately the chart keeps plotting the value spread over the coming months.

Yes, I have a relationship to the date table which is where the tricky part comes in (for me at least...)

i'm using the same source table to plot values over the coming months. Using that source table i would like to measure a different value and plot it cumulatively on the chart for the current month.

I'm not able to share the file or an example report...

To elaborate, an exmaple;

I have a table with production costs.

I would like to plot the expected costs spread over the coming months, that works fine.

When I try to add one column for the actual costs and plot it on the current month, the chart spreads it out over the coming months, i'm guessing because of the relationsship..

Can I remove the relationship filter using DAX for a single measurement?

Apologies for the potentially fuzzy explanation, but thanks in advance for any help 🙂

[v-chuncz-msft]

@PaulusD ,

You may add CROSSFILTER Function outside and set cross-filter direction to None.

[PaulusD]

Hi Sam,

You may add CROSSFILTER Function outside and set cross-filter direction to None.

---

Thanks for your reply.

I'm new to the crossfilter function and sounds like it should be something I can use, however my chart is still spreading the data over the remaining months.

I've added crossfilter as:

Total = CALCULATE(SUM(table1[VALUE]))+CALCULATE(SUM(table2[value]),table2filter[filtercolumn] = "filtervalue",CROSSFILTER(Datetable[Date],table2[Date],None))

In the above I have removed the table naming etc, hope it's still clear.

From what I read in the link to the function you posted, this should be the way to go.

is it a problem that i'm measuring two CALCULATE functions at once?

Thanks!

[PaulusD · Accepted solution]

So a workaround is a solution for now, which is adding the required table again in the editor (copy of the source table), and not linking that table to the date table (marked as date table).. this allows me to use the cumulative values in the current month.

Although I feel there should be solution for this in DAX without having to import the table twice.

Still open to sugustions to be able to "correct" this at a later point as crossfilter doesn't seem to be doing it for me.

Thanks!