Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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] )
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
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] )
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] )
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
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] ) )
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
87 | |
85 | |
74 | |
69 | |
65 |
User | Count |
---|---|
214 | |
124 | |
117 | |
82 | |
76 |