Forum Discussion
Displaying multiple dates across a single x axis
kenyonca You're correct, I don't think you would be able to use those filters. How about this as another option? What we need to do is unpivot your dates in the Query Editor. I added Location as a column in my sample dataset to use as an example.
In the Query Editor I selected my date columns and clicked Unpivot Columns
The result is this; you can rename "Attribute" and "Value" if you want, but now you have a list of dates that are being described by the attribute.
You can now set up a relationship between your Date table and your single Date column in your table
I can build a bar chart that looks like this:
And filter on location using a Slicer.
Does this meet all your requirements?
There might be a more-slick option for this, but my thought would be to do the following:
- Add 4 columns to your Date table - one for each date you want to track. The columns will be the count of each type of date you want to ID
- The formula for each column will be something like:
ActualConstructionStartDateCount =
CALCULATE(Countrows(WorkDate),
Filter(WorkDate,WorkDate[ActualConstructionStart] = 'Date'[Date])
)Where WorkDate is the table you share a screenshot of above. Result is something like this with the 3 records you shared:
Thoughts?
Dan
Thanks for the suggestion danrmcallister
I'm fairly new to Power BI
I'm trying out this solution but it wont seem to work. For reference my dates for the 4 different date sets are under the "CWP Status".
My Date Table just includes all dates from project start to finish.
Please see below shot
Sure kenyonca, so what I am suggesting is that you add a few columns to your date table - one each for each type of date you want counted. In your date table with all dates then you'll have a summary of how many projects have an "ActualConstructionStartDate" (etc) on that specific day. Please correct me if that's not what you are looking for!
Dan
Thats a good workaround for that issue thanks danrmcallister. I should maybe have mentioned there is still quite a few different filters that we are trying to run such as Discipline, Location, Work Area, Actual Labor MHRs, etc. This data is all on the same rows as those other dates.
With that when I try to apply those filters they dont make any changes to the data. My guess is this because the "count" function used doesnt take into account any of this information.
kenyonca You're correct, I don't think you would be able to use those filters. How about this as another option? What we need to do is unpivot your dates in the Query Editor. I added Location as a column in my sample dataset to use as an example.
In the Query Editor I selected my date columns and clicked Unpivot Columns
The result is this; you can rename "Attribute" and "Value" if you want, but now you have a list of dates that are being described by the attribute.
You can now set up a relationship between your Date table and your single Date column in your table
I can build a bar chart that looks like this:
And filter on location using a Slicer.
Does this meet all your requirements?