Forum Discussion
filling missing dates in dataset
Hi All,
i have a difficult scenario here, kindly provide your ideas if this can be possible to achieve.
say we have data for the month of january, we record data only on business days.
when ever there is a transaction happen on business day there will be entry.
if there is no any transaction there wont be any entry for that day.
so for January i have 2 records in my database.
when i select my slicer for January, my average DAX measure is showing average for only 2 days and it is wrong.
basically it shud consider 0 for the remaining missing BUSSINESS DAYS.
please check the data below.
when you write the average dax measure for 4th column it will give around 4 million.
but ideally it shud consider missing 0 for the missing business days. which is aroung 440K.
how to calculate these missing bussiness days in the DAX ??
| Account | Account Name | System Date | Maintenance Margin |
| 123 | Sample | 1/4/2021 | $0 |
| 123 | Sample | 1/5/2021 | $0 |
| 123 | Sample | 1/6/2021 | $0 |
| 123 | Sample | 1/7/2021 | $0 |
| 123 | Sample | 1/8/2021 | $0 |
| 123 | Sample | 1/11/2021 | $0 |
| 123 | Sample | 1/12/2021 | $0 |
| 123 | Sample | 1/13/2021 | $0 |
| 123 | Sample | 1/14/2021 | $0 |
| 123 | Sample | 1/15/2021 | $0 |
| 123 | Sample | 1/18/2021 | $0 |
| 123 | Sample | 1/19/2021 | $0 |
| 123 | Sample | 1/20/2021 | $0 |
| 123 | Sample | 1/21/2021 | $0 |
| 123 | Sample | 1/22/2021 | $0 |
| 123 | Sample | 1/25/2021 | $0 |
| 123 | Sample | 1/26/2021 | $0 |
| 123 | Sample | 1/27/2021 | $0 |
| 123 | Sample | 1/28/2021 | $4,400,000 |
| 123 | Sample | 1/29/2021 | $4,400,000 |
amitchandak PhilipTreacy selimovd Ashish_Mathur lbendlin MFelix Anonymous
4 Replies
- selimovd
Most Valuable Professional
Hey Anonymous ,
for me a simple average works:
Average Maintenance Margin = AVERAGE( myTable[Maintenance Margin] )Or did I miss something?
If you need any help please let me know.If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍Best regardsDenisBlog: WhatTheFact.bi - Ashish_Mathur
Super User
Hi,
You mentioned that there will not be any row if there is no business on a certain day. If that statement is correct then why do you have rows with 0 value in the sample data? Please clarify.
Hi Ashish_Mathur i just provided the sample data with 0 on when there is no business. but actually i have last 2 rows in my dataset. thnaks for your time in looking this.
- Ashish_Mathur
Hi,
There are 21 working days in January 2021. The average margin per working days is 419.05. You may download my PBI file from here.
Hope this helps.
