Forum Discussion

Miinch87's avatar
Miinch87
Frequent Visitor
3 years ago
Solved

Measure for Moving Average / Simple Moving Average Problem

Hello, i created a measure and i dont know why it shows the wrong result. The measure should calculate the moving average: the exptected result is 161 and but it shows 1932. Maybe its a data format...
  • Miinch87's avatar
    Miinch87
    3 years ago

    Good Morning,
    sorry for the delayed answer.

     

    i found out that the measure doenst consider the empty values -> "ledger entry months" ... on the other side if there are some postings in the previous months it works just fine. 

     

    If there are no posting in the previous 12 months than it shows the complete value = 1932 instead of 161 

     

    i changed the measure to: 

     

    i hope it fixes the Problem ... atleast it looks right

     

    Greetings

    Gleitender Durchschnitt = 

    VAR NumOfMonths = 12

    VAR LastCurrentDate =

       EOMONTH(TODAY(),-1)

    VAR Period =

        DATESINPERIOD ( Datum[Date] , LastCurrentDate, - NumOfMonths, MONTH )

    VAR Result =

        CALCULATE (

            DIVIDE(

                Verkaufstransaktionen[Fakt. Menge],

                12

            ),

            Period

        )

    VAR FirstDateInPeriod = MINX ( Period, Datum[Date]  )

    VAR LastDateWithSales = MAX ( Verkaufstransaktionen[Buchungsdatum] )

    RETURN

        IF ( FirstDateInPeriod <= LastDateWithSales, Result )
     
Miinch87's avatar
Miinch87
Frequent Visitor
3 years ago

Good Morning,
sorry for the delayed answer.

 

i found out that the measure doenst consider the empty values -> "ledger entry months" ... on the other side if there are some postings in the previous months it works just fine. 

 

If there are no posting in the previous 12 months than it shows the complete value = 1932 instead of 161 

 

i changed the measure to: 

 

i hope it fixes the Problem ... atleast it looks right

 

Greetings

Gleitender Durchschnitt = 

VAR NumOfMonths = 12

VAR LastCurrentDate =

   EOMONTH(TODAY(),-1)

VAR Period =

    DATESINPERIOD ( Datum[Date] , LastCurrentDate, - NumOfMonths, MONTH )

VAR Result =

    CALCULATE (

        DIVIDE(

            Verkaufstransaktionen[Fakt. Menge],

            12

        ),

        Period

    )

VAR FirstDateInPeriod = MINX ( Period, Datum[Date]  )

VAR LastDateWithSales = MAX ( Verkaufstransaktionen[Buchungsdatum] )

RETURN

    IF ( FirstDateInPeriod <= LastDateWithSales, Result )