I'm trying to figure out how to use two different dates to capture two different statuses: 'Open' and 'Closed'.
So with my date in the field "Start Date" is when the event was open and the date in "Event Disposition Date (All)" is the date it closed.
I had a similar problem last week, which I got a solution for, but had to create a date table to link the date fields to and now I can't figure out this issue again.
So created a table with the formula below and linked this field with the two fields mentioned above.
Each column is the total for each month, what I'm wanting though is to have it split out into 'Open' and 'Closed' dates. For example all the events in May 2021, I want to be in two columns for that month showing the number opened in the month (the "start date" field) and the number closed (the "Event Disposition Date (All)" field). So as well as the stacked data, seperated out clearly into the two conditions.
Last time around I was using the events' status, then took off label concantenate which gave this snapshot but it isn't the data I'm after.
Does anyone know how to show what I'm asking?
Solved! Go to Solution.
Hi @Kmcdonald ,
You can upload attachments by other means, and then just provide a link to the shared file on the forum.
Looking forward to your reply.
@Kmcdonald You can use Onedrive link to share files, or just past a small table directly in the post so we can copy. Screenshots of what isn't working like you've been sending are helpful too. I managed to dig up my clustered stacked bar chart. Hope it helps.
Here's the source data to save into csv if you want to edit it:
|Account Manager||CY A||CY B||PY A||PY B||CY Total||PY Total|
Hi @Kmcdonald ,
Whether the advice given by @AllisonKennedy has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly.
If not, can you provide some test data so that I can answer for you.
Looking forward to your feedback.
@AllisonKennedy have you got any links to give a run down on how to use these custom visuals?
They look quite complex from first glance and trying to figure out.
@Kmcdonald they're not straightforward. Might take at least a few days to a week to learn how to use them. I've done a stacked clustered column with Charticulator before. If I get time I may do a blog on how I did it.
In the meantime, try these links:
This is a DAX solution, so no Charticulator needed. I haven't looked at it in great detail so not sure how good it is, but you'll need to truly understand the concepts to apply it to your scenario as you're doing something a bit different:
@Kmcdonald You're on the right track and definitely need a date table. Create an inactive relationship between Event Disposition Date (All) and the Date column from your Calendar table.
Then create two measures to put in your chart:
Count Open = Countrows(table)
Count Closed = CALCULATE( COUNTROWS(table), USERELATIONSHIP( calendar[date], table[Event Disposition Date (All)] ) )
Now if you want to split this by classification also, you'll have to do it as a drill down or be a bit more creative. Hope that makes sense?
Thanks @AllisonKennedy , I created those measures but I can't get them into the shared axis of the chart?
The measure is formatted as a whole number but I can't move onto anything but the lines values or take over one of the column fields. That's unless there is something else I'm missing...
@Kmcdonald because they are measures, they can only go in Values. You could put classification in the shared axis and move them into the column values once the legend is empty, then drill down, but this won't give you the stacking you're looking for.
A custom visual like Charticulator or Deneb is the only way I know to create a clustered stacked column chart.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.