Forum Discussion

okusai3000's avatar
okusai3000
Icon for Helper IV rankHelper IV
5 years ago
Solved

Average last 30 days even without continuous data

Hello everyone,

 

I'm trying to get a column with the average of the last 30 days of data. So far, I thought this formula was ok:

 

AVG USAGE:

CALCULATE(AVERAGe('fact Monitorizacion'[HORAS_USO]),

DATESINPERIOD('dim Fecha 2'[Fecha],LASTDATE('dim Fecha 2'[Fecha]),-30,

DAY),

ALLEXCEPT('fact Monitorizacion','fact Monitorizacion'[ID_PACIENTE_INTERNO])) 

 

 

But our customer just told us that the days that doesn't appear on the data, ALSO must been inside of the calculation. In other words, If we only have data of 2 days on the week, we still have to make the average against 30 days, not only 2.

 

I've been trying to figure it out, but I'm not making it so far.

 

any ideas?

thanks!

Need Help
  • okusai3000's avatar
    okusai3000
    5 years ago

    I solved it. If anyone has this sort of problem, here goes the DAX:

     

    VAR USO_ULTIMOS_30dias=calculate(sum('fact Monitorizacion'[HORAS_USO]),DATESINPERIOD('dim Fecha 2'[Fecha],LASTDATE('dim Fecha 2'[Fecha]),-30,

    DAY),ALLEXCEPT('fact Monitorizacion','fact Monitorizacion'[ID_PACIENTE_INTERNO]))

     

    VAR DIAS_ANTIGUEDAD=DATEDIFF(CALCULATE(min('fact Monitorizacion'[*Fecha Recogida])

    ,ALLEXCEPT('fact Monitorizacion','fact Monitorizacion'[IdPrescripcion]))

    ,'fact Monitorizacion'[FECHA_RECOGIDA],DAY)+1

     

    RETURN

     

    if(DIAS_ANTIGUEDAD<30, divide(USO_ULTIMOS_30dias,DIAS_ANTIGUEDAD),divide(USO_ULTIMOS_30dias,30))

4 Replies

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    5 years ago

    Hey okusai3000 ,

     

    why don't you then use the SUM instead of AVERAGE of the last 30 days and then divide it by 30?

    Like this you get only 2 days divided by 30 or the values of the last 30 days divided by 30. That's at least what I understood what you want.

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
    • okusai3000's avatar
      okusai3000
      Icon for Helper IV rankHelper IV
      5 years ago

      Hi Denis!

       
      Our DB is based on treatments. We could just use the sum and divided by 30 to the patients that have been with us longer than 30 days. 


      The problem would come with the ones that have just started, for example, last week.  You simply cannot divide them by a 30. on this case should be all the days that have passed until the selected day. 

      is it clear?

       

       Thanks!!

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, okusai3000 

     

    You need to create a calendar table and a measure, Like this:

    Table = CALENDAR(MIN(Table1[date]),MAX(Table1[date]))

    Measure = 
VAR a =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        DATEDIFF ( 'Table'[Date], TODAY (), DAY ) = 30
    )
RETURN
    CALCULATE (
        AVERAGE ( Table1[number] ),
        FILTER ( ALL ( Table1 ), [date] >= a && [date] <= TODAY () )
    )

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • okusai3000's avatar
    okusai3000
    Icon for Helper IV rankHelper IV
    5 years ago

    I solved it. If anyone has this sort of problem, here goes the DAX:

     

    VAR USO_ULTIMOS_30dias=calculate(sum('fact Monitorizacion'[HORAS_USO]),DATESINPERIOD('dim Fecha 2'[Fecha],LASTDATE('dim Fecha 2'[Fecha]),-30,

    DAY),ALLEXCEPT('fact Monitorizacion','fact Monitorizacion'[ID_PACIENTE_INTERNO]))

     

    VAR DIAS_ANTIGUEDAD=DATEDIFF(CALCULATE(min('fact Monitorizacion'[*Fecha Recogida])

    ,ALLEXCEPT('fact Monitorizacion','fact Monitorizacion'[IdPrescripcion]))

    ,'fact Monitorizacion'[FECHA_RECOGIDA],DAY)+1

     

    RETURN

     

    if(DIAS_ANTIGUEDAD<30, divide(USO_ULTIMOS_30dias,DIAS_ANTIGUEDAD),divide(USO_ULTIMOS_30dias,30))