Average last 30 days even without continuous data

[okusai3000]

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!

[okusai3000 · Accepted solution]

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))

[v-janeyg-msft]

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.

[selimovd]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[okusai3000]

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!!