Forum Discussion

haputhanthree's avatar
haputhanthree
Frequent Visitor
3 years ago
Solved

Averagex over multiple dates

Hi, I have an unrelated calendar table and the below measures in the sales table.  InTransit =  CALCULATE(     COUNT(Sales[ID])     ,FILTER(         'Sales'         ,Sales[DeliveryDate] <...
Need Help
  • haputhanthree's avatar
    haputhanthree
    3 years ago

    The below measue worked for me. 

     Average Lst 2 Months =
    VAR NumOfMonths = 2
    VAR LastCurrentDate =
        MAX ( 'Dim Calendar'[Date] )
    VAR Period =
        DATESINPERIOD ( 'Dim Calendar'[Date], LastCurrentDate, - NumOfMonths, MONTH )
    VAR result =
        AVERAGEX (
            SUMMARIZE (
                CALCULATETABLE ( 'Dim Calendar', Period ),
                'Dim Calendar'[Month],
                "InTransit", [InTransit]
            ),
            [InTransit]
        )
    RETURN
        result

haputhanthree's avatar
haputhanthree
Frequent Visitor
3 years ago

The below measue worked for me. 

 Average Lst 2 Months =
VAR NumOfMonths = 2
VAR LastCurrentDate =
    MAX ( 'Dim Calendar'[Date] )
VAR Period =
    DATESINPERIOD ( 'Dim Calendar'[Date], LastCurrentDate, - NumOfMonths, MONTH )
VAR result =
    AVERAGEX (
        SUMMARIZE (
            CALCULATETABLE ( 'Dim Calendar', Period ),
            'Dim Calendar'[Month],
            "InTransit", [InTransit]
        ),
        [InTransit]
    )
RETURN
    result