Forum Discussion
Using a Date Table for a Visualisation
Hi Everyone
Im James - on the cusp of starting to use Power BI and all the amazing things it can do, Im limited in what I can share in this space but I think I’ve explained me need below.
The table in Yellow is my data source; which I’ve simplified. It has a description and a due date in dd/mm/yy
The table in Blue is a Visualization I want to achieve. I actually managed to do this in Power BI with a matrix table. (see Fig 2)
Now when I say Ive nearly achieved it- see Fig 2 which is what it looks like. I had no due dates in September onwards so there were no due dates in September to December. I wanted to Sep-Dec even if there were no dates. I may even want my matrix to be like Q1/Q2/Q3/Q4
So I believe I need a date table and I was just looking for advice on how you would achieve this?
Thanks for any advice.
James
Fig 1
Fig 2
You do need a date table! Luckily, they're pretty easy and there are plenty of people who have posted scripts to create date tables with all the columns you'll ever need! Watch the video linked below (for the how) and the date table I use is from the second link here!
https://www.youtube.com/watch?v=WybnTHDl-AM
https://radacad.com/all-in-one-script-to-create-date-dimension-in-power-bi-using-power-query
5 Replies
You do need a date table! Luckily, they're pretty easy and there are plenty of people who have posted scripts to create date tables with all the columns you'll ever need! Watch the video linked below (for the how) and the date table I use is from the second link here!
https://www.youtube.com/watch?v=WybnTHDl-AM
https://radacad.com/all-in-one-script-to-create-date-dimension-in-power-bi-using-power-queryby default, Power BI visualizations don't show dimension (date) members that have no data. So if your data ends in August, Power BI won't show you a September bucket. Think about it, it won't show you anything in 2024 either because there is no data for that year. Same for the next years, the next decade, the next century. Power BI is smart enough to stop the visualization when the data ends.
You just have to get past this.
Start with a Date table. You can do that with a simple DAX funtion:
My Dates = CALENDAR("01/01/2021", "12/31/2023")
Now join this table to your main table on the Date.
Next, what is the intersection on the matrix? What does that X represent? Is it a COUNT or SUM? If you simply drag the numeric column into the Values section of the matrix, Power BI will set up an aggregation on that column, usually SUM.
What you need to do is create your own MEASURE to be placed in the Values part of the Matrix. That Measure needs to report SOMETHING for when there is no data. Not sure if the below would work:
My Measure = IF ( ISBLANK(SUM([My Column]), " ", SUM ( [My Column] ) )
Next, put the Months (from the Date table) on the columns of the Matrix, and the new measure in the Values.
let me know if that works
Syk Firstly a Big Thanks for the advice on the date table. That has solved many other issues that I was not actually posting about - So thanks again.
ToddChitt Im still delving into the measures etc. Whilst taht is ongoing I atr least have a visulisation with some basic counting and conditinal formatting.
Thougg I could not seem to get my lets Milesstones (green icons) to appear in Date order frpm Left to Right.
All likley related to sorting etc
To fix this sorting issue you show, you need another column, maybe an INT, with values 1, 2, 3, that correspond to the Task. Then in Column Tools, make sure Task has a "Sort by Column" that is that INT. That puts "Some Task 10" AFTER "Some Task 9". Optioally, add a leading zero in the Task name: "Some Task 01"
