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
Super 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
    )

 

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

    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

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Super User
      4 years ago

      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.

       

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

        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