Average of a DISTINCTCOUNT per day

[ssaezgarcia]

Hello everyone, I'm new in DAX and I have a big problem with a measure (sorry my bad english). 

I need to calculate the average of Distinctcount for 7 days moving, for that, I'm counting distincts rows every day like that

Vehiculos Totales:=DISTINCTCOUNT(VEHICULOS[PATENTE])

Second, I need to do an average of last 7 days (73 + 96 + 96 + 100 + 36 + 34 + 42) / 7 = 68.14

But with my formula I'm getting 27.857 for date 02-11-2017

This is my formula:

PROM MOVIL CANT Vehiculos:=DIVIDE(CALCULATE([Vehiculos Totales];DATESINPERIOD(Fecha[Fecha]; LASTDATE(Fecha[Fecha]);-7;DAY));7)

With this formula I'm getting distinct rows in the last 7 days and that result is divide by 7, but I need to do the average of every day.

Please, help me in my problem.

Thanks to all.

Sebastian

[v-ljerr-msft]

Hi @ssaezgarcia,

Have you tried the solution provided by @Zubair_Muhammad above? Does it work in your scenario? If it works, could you accept it as solution to close this thread?

If you still have any question on this issue, feel free to post here. 

Regards

[Zubair_Muhammad]

Hi @ssaezgarcia

Sebastian

Try this MEASURE

PROM MOVIL CANT Vehiculos =
VAR Mydate =
    SELECTEDVALUE ( Fecha[Fecha] )
RETURN
    AVERAGEX (
        CALCULATETABLE (
            SUMMARIZE ( Fecha, Fecha[Fecha] ),
            DATESINPERIOD ( Fecha[Fecha], Mydate, -7, DAY )
        ),
        [Vehiculos Totales]
    )

[Zubair_Muhammad · Accepted solution]

Hi @ssaezgarcia

Please ingore above code. Summarize and CalculateTable are extraneous/unnecessary

Please try this. I believe you are using Dates from Fetcha Table in the VISUAL and there is a relationship between FETCHA and VEHICULOS

PROM MOVIL CANT Vehiculos =
VAR Mydate =
    SELECTEDVALUE ( Fecha[Fecha] )
RETURN
    AVERAGEX (
        DATESINPERIOD ( Fecha[Fecha], Mydate, -7, DAY ),
        [Vehiculos Totales]
    )

[ssaezgarcia]

Thanks @Zubair_Muhammad for your answer, but I have a problem for to test the code, my version of DAX is office 2013, VAR and SELECTEDVALUE functions are not recognized.

I'm inside a company that doesn't allow to perform Office updates.

Thanks anyway.

Sebastian

[Zubair_Muhammad]

Hi Sebastien @ssaezgarcia

In that case try this code

PROM MOVIL CANT Vehiculos =
IF (
    HASONEVALUE ( Fecha[Fecha] ),
    AVERAGEX (
        DATESINPERIOD ( Fecha[Fecha], VALUES ( Fecha[Fecha] ), -7, DAY ),
        [Vehiculos Totales]
    )
)