Forum Discussion
Adding missing dates each category for a kpi dashboard
Hello - We are setting up a KPI dashboard we have target value of 10 records each day per Property Name . I have already aggrigated the data for the number of records per day. This shown below. My problem is that I have missing dates as there were no records generated for that day.
We need to fill in each missing date field per property. I would be onen to using the show missing data in the calandar table however the I cannot figure out how I would assign that target.
4 Replies
Create a date calendar table and join date with that date. Then use the option to display 0 data rows and filter visual where value =0.
When you put in visual use the date from date table.
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/Thanks for the repy. However, I am not seeing where this would add dates for all the different properties. The first set of data below is the original data and the second one is what it needs to show.
Data Set Original Property Name Date 5Fifty5 1/18/2020 5Fifty5 1/20/2020 5Fifty5 1/22/2020 5Fifty5 1/24/2020 5Fifty5 1/27/2020 7th Street Brownstones 1/18/2020 7th Street Brownstones 1/20/2020 7th Street Brownstones 1/22/2020 7th Street Brownstones 1/24/2020 7th Street Brownstones 1/27/2020 Transform to: 5Fifty5 1/18/2020 5Fifty5 1/19/2020 5Fifty5 1/20/2020 5Fifty5 1/21/2020 5Fifty5 1/22/2020 5Fifty5 1/23/2020 5Fifty5 1/24/2020 5Fifty5 1/25/2020 5Fifty5 1/26/2020 5Fifty5 1/27/2020 7th Street Brownstones 1/18/2020 7th Street Brownstones 1/19/2020 7th Street Brownstones 1/20/2020 7th Street Brownstones 1/21/2020 7th Street Brownstones 1/22/2020 7th Street Brownstones 1/23/2020 7th Street Brownstones 1/24/2020 7th Street Brownstones 1/25/2020 7th Street Brownstones 1/26/2020 7th Street Brownstones 1/27/2020 Hi corbincdc ,
Please check if this can meet your requirements:
1. Create a Calendar table.
Calendar = CALENDAR( MIN( 'Table'[Date] ), MAX( 'Table'[Date] ) )2. Create relationships between the two tables.
3. Create Measures.
For numerical columns, create measures like so:
Value Measure = SUM( 'Table'[Value] ) + 0For text columns, create measures like so:
Text Measure = IF ( MAX ( 'Table'[Text] ) = BLANK (), "NULL", MAX ( 'Table'[Text] ) )4. Create Table visuals.
For more details, please check the attached PBIX file.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
