Create Dax measure to find missing days & fetch the value for those days that falls between 2 dates

[sivarajan21]

Hi 

I am trying to create two dax measures for our report but confused how to incorporate them.

Below screenshot provides a sample data and algorithm examples on how to create dax measure:

The calendar tables have all days of financial years.

But the data invoice table contains only the dates for which their points id were invoiced.

General terms for your information:

Forecast is the missing day in invoice table

Each utility will have different factor value

Each DBName will have different Utility as below

Please help me create below 2 dax measures on the attached file that contains real data:

1)Forecast Units

2)Factor Value

How to get the forecast Units measure is explained in above excel screenshot and adding to above i have added the below:

• Find the missing dates of invoice table by comparing to calendar table & also for those missing dates, find missing points of invoice table from points table
• Then after finding above, for the current filter context, find the target value from target table where target type =0, for corresponding point id and corresponding month(because target table contains month only).
• After finding target value, divide by no of days for their respective month to get daily target units.

Factor value will contain below:

• Finding the missing date and point id is similar to above
• In addition to that, for this filter context, we need to find the factor value from factor table where the date will fall between start and end date of factor table and point id is equal to point table point id, which in turn references their corresponding DBName and utility of factor table.

PFA file here Portfolio 6 latest (2) - Copy (1).pbix

sample data here Carbon.xlsx

Please help me to resolve this

Thanks in advance!

@amitchandak @Ahmedx @Greg_Deckler @marcorusso 

[v-yaningy-msft]

Hi, @sivarajan21 

Because of the privacy policy, I can not open the pbix file you provided for the time being, you can use Google Drive to share the data and set it up without logging into the account.

Best Regards,
Yang
Community Support Team

[sivarajan21]

Hi,

We have made some progress to this question and here is below dax measure:

The measure as below:

Pre_forecast_TCarbon =
VAR _Totalunits =
CALCULATE ( SUM ( Target[Value] ), FILTER ( Target, Target[TargetType] = 0 ) )
VAR _noofdays = [no of days]
VAR _DailyUnits =
DIVIDE ( _Totalunits, _noofdays )
VAR _Factor =
CALCULATE (
AVERAGE ( Factor[Value] ),
FILTER (
Factor,
Factor[StartDate] <= MAX ( Calendar_[Date] )
&& Factor[EndDate] >= MAX ( Calendar_[Date] )
&& Factor[DBName - Utility] IN VALUES ( Points[DBName-Utility] )
)
)
RETURN
CALCULATE (
SUMX ( 'Calendar_', _DailyUnits * _Factor ),
FILTER ( Calendar_, ISBLANK ( [Actual Units] ) )
)

When we drag this measure into visual, we found the below issue:

The total was showing 698.43 instead of 458.17. 

We have broken down the above measure(highlighted in yellow) into two different measures(orange color) for our reference purpose as shown below:

Multiplication of these two orange color measures should give the highlighted value in yellow.

The expected output should be total row showing 458.17 for that measure similar to row level.

Could you please help us resolve this issue?

Thanks in advance!

@v-yaningy-msft @marcorusso @Greg_Deckler @Ahmedx @amitchandak @MFelix 

[MFelix]

Hi @sivarajan21 ,

On  the measure you shared there is the use of [no days] however in the PBIX file that measure does not exist can you share how that measure is calculated please.

[sivarajan21]

Hi @MFelix 

Thanks for your response! 
Apologies for not providing the code for no of days,please find 
no of days =
CALCULATE (
COUNTROWS ( 'Calendar_' ),
ALL ( 'Calendar_' ),
VALUES ( 'Calendar_'[Month-Year] )
)

Also, found that below factor 1 measure that i created doesn't display any values :

Factor 1 =
VAR _factor =
CALCULATE (
AVERAGEX ( Factor, Factor[Value] ),
FILTER (
Factor,
Factor[StartDate] <= MAX ( Calendar_[Date] )
&& Factor[EndDate] >= MAX ( Calendar_[Date] )
&& Factor[DBName - Utility] IN VALUES ( Points[DBName-Utility] )
)
)
RETURN
SUMX ( Points, _factor )

Can you please help me with above issues? 

PFA updated file here 

https://drive.google.com/file/d/1PunI-tfbHW8UXyro6Af7P0ATSP1hjrVq/view?usp=sharing

Thanks in advance!

[sivarajan21]

Hi @v-yaningy-msft ,

Thanks for your quick response!

I have shared now in google drive and put the link to access for anyone. Can you please check now?

PFA link for pbix file
here https://drive.google.com/file/d/1qE6I_SDjRLXsrJM70RDTiYK0IN8Q2Dut/view?usp=sharing

PFA link for sample excel file
here https://docs.google.com/spreadsheets/d/1YBEoBrNbf5MYZGTr-6zqRgzmluM4j6ua6ALK6l4KVKQ/edit?usp=sharing

Please let me know if you still face the issue!

Thanks in advance!

@Ahmedx @marcorusso @amitchandak @Greg_Deckler @AmiraBedh @lbendlin @Idrissshatila @Ritaf1983 @Daniel29195 @danextian @MFelix @DataInsights