cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PaulusD
Helper I
Helper I

cumulative column in a bar chart created by month

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 🙂

1 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!

View solution in original post

3 REPLIES 3
v-chuncz-msft
Community Support
Community Support

@PaulusD ,

 

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

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

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!

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!

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors