Forum Discussion

Excellove15's avatar
Excellove15
Iron Contributor
Mar 11, 2024

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

Hi SergeiBaklan 

 

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

Below screenshot provides a sample data table 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:

1)Forecast Units

2)Factor Value

 

How to get the forecast Units measure logic 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 dax measure will contain below logic:

  • 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

Please help me to resolve this

 

Thanks in advance!

SergeiBaklan 

BI & Data Analysis
Charting
developer
excel
Formulas and Functions
Macros and VBA
office 365
Power BI
    • Excellove15's avatar
      Excellove15
      Iron Contributor
      Mar 11, 2024

      Hi SergeiBaklan ,

       

      Thanks for your quick response Sir!

      Yes Sir! It was the second question of my previous post.

      It required brief information to be provided so thought I would raise the second question in a separate post.

      Also, thought one question in a post will avoid confusion sir:smile:

       

      PFA sample excel file here Carbon.xlsx

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

       

      Thanks in advance!

       

      • Excellove15's avatar
        Excellove15
        Iron Contributor
        Mar 18, 2024

        Hi SergeiBaklan 

        Just to confirm, have you made any progress on this?

        If not nop! we have made some progress sir!

        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 sir!

Resources