filling missing dates in dataset

[Anonymous]

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 

[Ashish_Mathur]

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.

[Anonymous]

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.

 

[selimovd]

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