Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
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
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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
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.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
69 | |
35 | |
21 | |
18 | |
15 |
User | Count |
---|---|
127 | |
32 | |
27 | |
24 | |
22 |