topic filling missing dates in dataset in DAX Commands and Tips

filling missing dates in dataset

Anonymous — Fri, 26 Mar 2021 14:04:14 GMT

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

Re: filling missing dates in dataset

selimovd — Fri, 26 Mar 2021 16:18:03 GMT

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 regards

Denis

Blog: WhatTheFact.bi

Re: filling missing dates in dataset

Ashish_Mathur — Fri, 26 Mar 2021 23:35:16 GMT

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.

Re: filling missing dates in dataset

Anonymous — Sat, 27 Mar 2021 01:59:54 GMT

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.

Re: filling missing dates in dataset

Ashish_Mathur — Sat, 27 Mar 2021 05:02:47 GMT

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.