Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX Help : Previous 3 Months average

​Hi all, hope you are doing well

I'm trying to create a measure to get Previous 3 month average, yet could not figure out on how to do this in DAX. For example, based on the the picture attahced below: 

-Jan can be blank 

-Febuary can be blank

-March can be blank

-April onwards will sum every number from January to March and devide by 3, because we are looking at the average of previous 3 month. 

-May will sum every number from February to April and devide by 3

 

 

Any help is very much appreciated. Thank you in advance.

10 Replies

  • SpartaBI's avatar
    SpartaBI
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous in your photo is that a data table and you need a calculated column or that is a visual table and you want to add a measure? If it's a visual what is the measure you have there for Lead Time (Days)?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      is actually a table that need to calculated a column ya. 

      • SpartaBI's avatar
        SpartaBI
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Anonymous 

         

        Average of Previous 3 Full Months = 
VAR _current_date = 'Table'[GRN Date]
VAR _end_point = EOMONTH(_current_date, -1)
VAR _start_point = EOMONTH(_current_date, -4) + 1
VAR _min_date_total = MIN('Table'[GRN Date])
VAR _result = 
    DIVIDE(
        SUMX(
            FILTER(
                'Table',
                'Table'[GRN Date] >= _start_point && 'Table'[GRN Date] <= _end_point
            ),
            'Table'[Lead Time (Days)]
        ),
        3
    ) 
RETURN
    IF(
        EOMONTH(_start_point, -1)  >= EOMONTH(_min_date_total, -1),
        _result
    )

         

         


        In case it answered your question, please accept the solution to help other members find it. Appreciate Your Kudos.

        Showcase Report – Contoso By SpartaBI


        Visit SpartaBI website   Visit SpartaBI Linkdin   Visit SpartaBI Facebook

        SpartaBI Logo