the beginning graph of a Moving average

[Applicable88]

Hello,

I observing the 180 Day moving average of failure incidents with that measure:

MA180 =

Var Calc =

CALCULATE(DISTINCTCOUNT('Errortable'[Incidents])/6, DATESINPERIOD('Errortable'[Date].[Date],LASTDATE('Errortable'[Date].[Date]),-6,MONTH),ALLSELECTED('Errortable'[Incidents]))

RETURN

IF(MAX('Errortable'[Date].[Date])>TODAY(),BLANK(),Calc)

What I observe the MA comparing to another BI-Software like Qlik the beginning of the curve is totally different. Here are two screenshots of the starting point of the chart. Look how PowerBi MA graph startsfrom bottom and Qlik MA starts already higher:

PowerBi:

Same in Qlik:

Qlik can automatically calculates the MA where Data is given and interpolates, but for PowerBi I only get the exact same curve like  Qlik as long we are in the 180Days range. Everything earlier is making the curve diminishing, it actually heads to Zero starting point.

Is it possible to rewrite the measure to have the same effect as Qlik? Otherwise the graph is a misleading when we want to look at the data older than 180 days.

Thank you very much in advance. 

Best. 

[v-yalanwu-msft]

Hi, @Applicable88 ;

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as solution to close this topic and help the others in the community find the solution easily if they face the same problem with you?

Thank you!

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Applicable88]

Hi @v-yalanwu-msft , its not solved, but I don't have example data now. With your approach from the last post, the graph is simply much more lower, which is a false expression of the real data. If I repeat whats the problem, its seen in the two screenshot as comparison. The PowerBI always goes down at the beginning of the graph. So actually what we have as MA gapf today should be also displayed correct 180Day from now. (180Days MA), but according to that now, it will fall down. Which is only correctly displayed within Qlik, where it represent the graph what it also was back then. 

[v-yalanwu-msft]

Hi, @Applicable88 
You could try to mofidy the measure as follows:

MA180 =
VAR Calc =
    CALCULATE (
        DISTINCTCOUNT ( 'Errortable'[Incidents] ) / 6,
        DATESINPERIOD ( 'Errortable'[Date], LASTDATE ( 'Errortable'[Date] ), -6, MONTH ),
        ALLSELECTED ( 'Errortable'[Incidents] )
    )
RETURN
    IF ( MAX ( 'Errortable'[Date] ) > TODAY (), BLANK (), Calc )

Here I delete .Date .

If it not right, Looking forward to your simple data.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson]

Instead of dividing by a constant 6, I suspect you want to divide by the number of available months. Try using a distinct count on whatever column you are using for the x-axis as the denominator.

[Applicable88]

Hello @AlexisOlson, alright I tried using distinctcount( Date) as denominator. I got the exact same results as with a constant 6. I don't understand why the graph doesn't calculate the dates "older than 180" the same as within 180 days and constantly going down.

Actually shouldn't the calculation always return the data based on the last 180 days no matter which timeline back I'm looking at? 

Best. 

[Fowmy]

@Applicable88 

Can you try this measure please:

MA180 =
VAR __period =
    DATESINPERIOD (
        'Errortable'[Date].[Date],
        LASTDATE ( 'Errortable'[Date].[Date] ),
        -6,
        MONTH
    )
VAR Calc =
    CALCULATE (
        DISTINCTCOUNT ( 'Errortable'[Incidents] ) / 6,
        __period,
        ALLSELECTED ( 'Errortable'[Incidents] )
    )
RETURN
    IF (
        MAX ( 'Errortable'[Date].[Date] ) > TODAY (),
        BLANK (),
        IF ( COUNTROWS ( __period ) > 180, Calc )
    )

[Applicable88]

Hi @Fowmy thanks for your effort. But I get exactly the same graph like mine above. There is no difference. 

Best.

[Fowmy]

@Applicable88 

I am not sure how you have set up your model and the measure is used. Can you share a sample PBIX file to understand? You can save the file in OneDrive or any other location and share the link here.
  

[Applicable88]

@Fowmy , I cannot share the data publicly. But let me check if I can make some sample data. 

Thank you very much.

Best.