Forum Discussion

PBI-Dash's avatar
PBI-Dash
Frequent Visitor
4 years ago

Calculating average between start and end dates

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 correc...
DAX
power bi
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
4 years ago

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
    )