Forum Discussion
Fill missing dates with 0's and empty cells
Hello all,
I have a sample table which countains 4 columns; Name, Date, Steps and Terrain. Not every name has data on each date, and I want to fill those missing dates. I want to fill the Steps column with 0 and want to leave the Terrain column empty (null value). In this sample Peter contains all dates in the dataset but ideally I want to fill based on the min and max Date value. I have come across several solutions which come close but I can't quite manage to get them to work for my situation. Below are examples of my data and the desired result:
Zepox Try these steps:
Step 1: Create a new calendar table using DAX
Calendar = CALENDAR(MIN('Step Table'[Date]),MAX('Step Table'[Date]))Step 2: Join date of this calendar table with Step table's date columnStep 3: Create a measure in Calendar table:
Steps Measure = SUM('Step Table'[Steps])+0Step 4: Create an another measure for Terrain in the same Calendar tableTerrain = IF(ISBLANK(MAX('Step Table'[Terrain])),BLANK(),MAX('Step Table'[Terrain]))Step 5: Final Output
2 Replies
Zepox Try these steps:
Step 1: Create a new calendar table using DAX
Calendar = CALENDAR(MIN('Step Table'[Date]),MAX('Step Table'[Date]))Step 2: Join date of this calendar table with Step table's date columnStep 3: Create a measure in Calendar table:
Steps Measure = SUM('Step Table'[Steps])+0Step 4: Create an another measure for Terrain in the same Calendar tableTerrain = IF(ISBLANK(MAX('Step Table'[Terrain])),BLANK(),MAX('Step Table'[Terrain]))Step 5: Final OutputThank you for your reply, this works as intended and translates to my actual data very well.
I still have some issues trying to make calculations based on the generated data, but I will figure those out along the way. Thanks again!