Forum Discussion
Cumulative Stakced column chart
I am trying to make a cumulative stacked column chart using different measures. The chart will be a month over month and year to date. I use a date slicer to choose the date range that I am looking for. This way I can choose dates in 2019 and see 2019, or choose 2018 and see 2018. How do I make the below measure display a cumulative sum month over month?
Hi, Anonymous
According to your description, the problem seems to exist in the relationship between your ‘Date’ table and the main table.
I also went back to my test pbix file, and also created a Calendar table like this:
Calendar = CALENDARAUTO( )Then I create a Many to one and Both filter relationship between the two tables, like this:
Then I changed the Date columns in the charts and Slicers with the date columns in the Calendar table, my chart can also work normally like before:
You can download my test pbix file here
You can also go to check your relationship between tables and find if there is a problem.
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Anonymous what is not working? Can you show sample data and screenshots, what is not working as expected or where you are getting the unexpected result?
Hi, Anonymous
According to your description, you want to make a measure to display a cumulative sum month over month in a stacked column chart, I’ve created some test data to achieve this, you can try my measure:
This is part of my test data based on your requirement:
cumulative sum month over month = CALCULATE( SUM(Sheet1[Amount]), FILTER( ALLSELECTED(Sheet1), MONTH([Date])<=MONTH(MAX([Date]))&& YEAR([Date])=YEAR(MAX([Date]) )))Then I created a stacked column chart and placed like this, and two Slicers to slice the data based on Year and Month, like this:
And you can get what you want, like this(If you want to see the data in 2018):
You can download my test pbix file here
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for your help. You solution works if I do not use my date table and use the date column in the table itself. I created a date table and created a relationship to all my tables and then created measures. But my date slicer will not effect the measures in my visuals.
The two measure examples below seem to be a good example of my issues. The first one uses a date hiearchy from my Date Table and the secone one does not. The first measure will NOT give my visual month over month but the second one will. I have no idea why the hiearchy would effect the measures like this. Also, neither of these will work with my date slicer I have using my Date Table. I wish I could share this with you but it is sensitive data and I cannot publish it.
I could set up each page with it's own Year filter but I would rather choose the year on my main page and let it control all of my other pages. I have checked to make sure my slicers are synced also. I have built other pbix visuals but this is the first time I have had this many issues with measures and date slicers.
CONT_SAV_CORP = CALCULATE([CONTRACT_CON_CORP]-[Vendor_CONTRACT_CORP], DATESYTD('Date Table'[Date])) - Date Hiearchy
CONT_SAV_CORP = CALCULATE([CONTRACT_CON_CORP]-[Vendor_CONTRACT_CORP], DATESYTD('Date Table'[DateUsed])) - Non Hiearchy
Hi, Anonymous
According to your description, the problem seems to exist in the relationship between your ‘Date’ table and the main table.
I also went back to my test pbix file, and also created a Calendar table like this:
Calendar = CALENDARAUTO( )Then I create a Many to one and Both filter relationship between the two tables, like this:
Then I changed the Date columns in the charts and Slicers with the date columns in the Calendar table, my chart can also work normally like before:
You can download my test pbix file here
You can also go to check your relationship between tables and find if there is a problem.
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
