topic Re: Calculating average between start and end dates in DAX Commands and Tips

Calculating average between start and end dates

PBI-Dash — Mon, 30 May 2022 11:35:56 GMT

Hi,

I'm struggling with a DAX expression where I want to calculate the average volume between a start and end date and after that applying this measure to a visual where the numbers are shown correctly.

The dataset is very simple and looks like this:

Opportunity_Id	Est Volume	Opportunity Date Contract Start	Opportunity Date Contract End
123XXX	100	2021-08-12	2022-05-30
456XXX	5	2021-09-01	2022-06-15
789XXX	6	2022-01-17	2022-12-31
321XXX	50	2022-06-20	2022-09-29

And I have a calendar table that has a relationship with this fact table through 'Calendar'[Date] 1:* 'fact_opportunites'[Opportunity_Date_Contract_Start].

Let's take an example:

We have an opportunity where the estimated volume is 100. The contract lenght is 5 months. In this case I want to take the sum of the volume (100) and divide it by the number of months (5). And then show this number (20) across those 5 months that this contract is valid. Is there any way to do this?

I've tried following this guide, but the result is that all the numbers are shown on either the month the contract starts or ends (depending on if the relationship is to 'fact_opportunites'[Opportunity_Date_Contract_Start] or 'fact_opportunites'[Opportunity_Date_Contract_End]). The measure looks like this:

Average estimated volume per Month = CALCULATE( SUMX( SUMMARIZE( filter( CROSSJOIN('fact_opportunities','Calendar') ,'Calendar'[Date] >= 'fact_opportunities'[Opportunity_Date_Contract_Start] && 'Calendar'[Date]<= 'fact_opportunities'[Opportunity_Date_Contract_End] ) ,'fact_opportunities'[Opportunity_Id] ,'Calendar'[Date] ,fact_opportunities[Opportunity_Estimated_Volume] ,fact_opportunities[Opportunity_Date_Contract_Start] ,fact_opportunities[Opportunity_Date_Contract_End] ) ,DIVIDE( [Volume] , DATEDIFF(fact_opportunities[Opportunity_Date_Contract_Start],fact_opportunities[Opportunity_Date_Contract_End],MONTH)+1) ) )

So the average numbers are showing correctly, just that they are only showing on either the start or end month (depending on the relationship).

Or does somebody have a different approach on how to solve this?

Thank you in advance!

Re: Calculating average between start and end dates

Jihwan_Kim — Mon, 30 May 2022 12:16:09 GMT

Hi,

I am not sure how your expected outcome of the visualization looks like, but please check the below picture and the attached pbix file.

I created no-relationship between the two tables in order to create a measure in a more easy way.

Monthly expected volume measure: = VAR _startingmonth = EOMONTH ( SELECTEDVALUE ( Data[Opportunity Date Contract Start] ), -1 ) + 1 VAR _endingmonth = EOMONTH ( SELECTEDVALUE ( Data[Opportunity Date Contract End] ), 0 ) VAR _monthcount = COUNTROWS ( SUMMARIZE ( FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] >= _startingmonth && 'Calendar'[Date] <= _endingmonth ), 'Calendar'[Month & Year CC] ) ) VAR _EstVolumeAVG = DIVIDE ( SUM ( Data[Est Volume] ), _monthcount ) RETURN IF ( MIN ( 'Calendar'[Date] ) >= _startingmonth && MAX ( 'Calendar'[Date] ) <= _endingmonth, _EstVolumeAVG, 0 )

Re: Calculating average between start and end dates

PBI-Dash — Mon, 30 May 2022 13:40:22 GMT

Hi,

Thanks for the reply!

Sorry, I forgot to mention how I'd like to visualize it.

In this case I'd like to visualize the numbers in a line chart where I also show the orders current year as well as orders last year. So this measure would somehow need to be connected to the calendar table that I'm using as an axis.

Sincerely

Dash

Re: Calculating average between start and end dates

Jihwan_Kim — Mon, 30 May 2022 14:29:06 GMT

Hi,

Thank you for your feedback.

I am not sure if I understood your inquiry correctly, but please check the below picture and the attached pbix file.

Re: Calculating average between start and end dates

PBI-Dash — Tue, 31 May 2022 08:35:08 GMT

Hi,

Sorry, I was a bit unclear.

I need the measure in the same visual as my other measures that are depandant on the calendar table. So I have two different fact-tables, one with orders and one with estimated volume. Both are connected to the calendar table at the moment. And I would be very happy if it was possible to have all measures in the same line graph.

Sincerely

Dash