Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 f...
Need Help
  • v-robertq-msft's avatar
    v-robertq-msft
    5 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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

v-robertq-msft's avatar
v-robertq-msft
Icon for Community Support rankCommunity Support
5 years ago

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.