Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
First of all, I'd like to introduce what I'm trying to get. I have the following table, where Antiguedad means how many days has that device been without changing their status. In other words, how long has been "AVAILABLE".
To get that Antiguedad I create a Measure that works fine:
*Antiguedad:
= DATEDIFF(
(MAXX(ALLSELECTED('dim Fecha'),Min('dim Fecha'[Fecha]))),[*Fecha Ultimo movimiento],DAY)
It's the difference between the month selected, and the date of the last status change.
*Fecha Ultimo movimiento:=
VAR fecha_ultimo_mov =
CALCULATE(
LASTDATE('fact Logistica_StockEquipos'[FECHA]),filter(all('dim Fecha'[Fecha]),'dim Fecha'[Fecha]<MIN(('dim Fecha'[Fecha]))),filter(all('dim Logistica_TipoEstado'[descrip_estado]),'dim Logistica_TipoEstado'[descrip_estado]<>SELECTEDVALUE('dim Logistica_TipoEstado'[descrip_estado])))
RETURN
if(ISBLANK(fecha_ultimo_mov),CALCULATE(FIRSTDATE('fact Logistica_StockEquipos'[FECHA]),all('dim Fecha')),fecha_ultimo_mov)
So far, so good, but I would like to get the average of days from that table. In other words, it's an average based on a measure and in order to get it right, I was told to use the function SUMMARIZE.
So, it goes like this:
Solved! Go to Solution.
@okusai3000 It is a bit hard to troubleshoot without the pbix file because the measures are a bit complex. My first try would be to see if this gives you the value you are looking for:
*Antiguedad AVG =
AVERAGEX ( DISTINCT ( 'fact Logistica_StockEquipos'[Esap_cod] ), [antiguedad] )
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
@okusai3000 It is a bit hard to troubleshoot without the pbix file because the measures are a bit complex. My first try would be to see if this gives you the value you are looking for:
*Antiguedad AVG =
AVERAGEX ( DISTINCT ( 'fact Logistica_StockEquipos'[Esap_cod] ), [antiguedad] )
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
Hey @DataZoe ! Your solution is almost correct! Now it makes the average for a couple of the Articles, but not for all of them. Any other idea?
https://ibb.co/kgBn91whttps://ibb.co/2hM7KMf
thanks!
@okusai3000 Is it possible to give me the file with some fake data? I am not really sure without looking at the other measures.
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
Hey Zoe, I realized that issue comes with the filtered data from my DrillTrought. Your help has been enough, thanks a lot!! 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |