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 b...
  • SpartaBI's avatar
    SpartaBI
    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

  • SpartaBI's avatar
    SpartaBI
    4 years ago

    Anonymous we are not dealing here with best practice stuff, but, as we created the column before you can create this measure to use as the line value:

    Average of Previous 3 Full Months Measure Dependant on Coloumn =
    AVERAGE('Table '[Average of Previous 3 Full Months Calculated Column])


    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

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

Anonymous we are not dealing here with best practice stuff, but, as we created the column before you can create this measure to use as the line value:

Average of Previous 3 Full Months Measure Dependant on Coloumn =
AVERAGE('Table '[Average of Previous 3 Full Months Calculated Column])


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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi SpartaBI , thanks for the clarification !!! appreciate!

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      yup ! have mark that as solution already !

      will definetely check on the report !

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      SpartaBI , do you have any experience on automating the process of extrating data from different data source ? such as Oracle ?

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

        Anonymous not sure what you meant?
        1. Extracting data from oracle to Power BI? (In this case you need an oracle client and a Power BI gateway)
        2. Creating an automation process that will create a DW from the data in Oracle in something that is more suitable for Power BI like Azure SQL?