CODEWITHSUNDEEP
powerbipowerbi-datasource
Raihan Nisreen
Raihan Nisreen

Reputation: 3

How to display missing dates in the data table Power BI

My data is all about vehicle movement. My current data is only show car that move on some particular date only. For example, if the car is not moving, the data with corresponding date is not stored. In this table, it shows that the car move on some particular date but some date are missing/not showing:

     Date        Count ASSET_ID       Mileage
*****************************************************
       1/7/2021            1               200
       4/7/2021            1                32
      18/7/2021            1               100

After the modifications, I would like to display the all date and the date that the car not moving is stored as zero. So I can count how many data for car not move in 1 month. Here the example table after the modifications that I want:

 Date         Count ASSET_ID          Mileage
****************************************************
    1/7/2021            1                  200
    2/7/2021            0                   0
    3/7/2021            0                   0
    4/7/2021            1                  32
    5/7/2021            0                   0
    6/7/2021            0                   0
    7/7/2021            0                   0
    8/7/2021            0                   0
    9/7/2021            0                   0
   10/7/2021            0                   0
   11/7/2021            0                   0
   12/7/2021            0                   0
   13/7/2021            0                   0
   14/7/2021            0                   0
   15/7/2021            0                   0
   16/7/2021            0                   0
   17/7/2021            0                   0
   18/7/2021            1                  100
   19/7/2021            0                   0
   20/7/2021            0                   0
   21/7/2021            0                   0
   22/7/2021            0                   0
   23/7/2021            0                   0
   24/7/2021            0                   0
   25/7/2021            0                   0
   26/7/2021            0                   0
   27/7/2021            0                   0
   28/7/2021            0                   0
   29/7/2021            0                   0
   30/7/2021            0                   0 
   31/7/2021            0                   0

Upvotes: 0

Views: 2249

Answers (1)

user10471965
user10471965

Reputation:

Adding a data table would help you tremendously here.

You can use the following one (Or edit it as you please):

dimDate = ADDCOLUMNS (
    CALENDAR (DATE(2021,1,1),DATE(2025,12,31)),
    "DateNumber", FORMAT([Date],"YYYYMMDD"),
    "Year", YEAR([Date]),
    "MonthNo",FORMAT([Date],"MM"),
    "YearMonthNo", FORMAT([Date], "YYYY/MM"),
    "YearMonthShort", FORMAT([Date], "YYYY/mmm"),
    "MonthShort", FORMAT([Date], "mmm"),
    "Month", FORMAT([Date], "mmmm"),
    "DayNo", WEEKDAY([Date]),
    "Day", FORMAT([Date], "dddd"),
    "DayShort", FORMAT([Date], "ddd"),
    "Quarter", FORMAT([Date], "Q"),
    "YearQuarter", FORMAT([Date], "YYYY") & "/Q" & FORMAT([Date], "Q"))

After adding the date table and connecting it in your data model, you can use measures to calculate what days the vehicle moved / did not move.

Upvotes: 1

Related Questions