Rolling 7 day average from calculated field

[nshindel]

Hi

My data looks like this

ID          Date             Eliglble

ABC        9/1/2018      1

DEF        9/1/2018       0

HIJ         9/1/2018       1

LMN       9/2/2018       0

OPQ       9/2/2018       0

RST        9/2/2018       1

All the IDs are distinct. I am looking for a percentage of IDs that are eligible, over time. That has been solved simply with a new calculated measure 

PercentEligible = SUM(DIADOSCE[Eligible]) / COUNT(DIADOSCE[ID])

From there, I am trying to get a rolling 7 day average, but PBI doesnt seem to handle how to do that on a calculated field. I tried this

7DayAverage = 
CALCULATE(
SUM(DIADOSCE[Eligible]) / COUNT(DIADOSCE[ID]),
DATESINPERIOD(DIADOSCE[Date], LASTDATE(DIADOSCE[Date]),7,DAY)
) 
/
CALCULATE(
DISTINCTCOUNT(DIADOSCE[Date]),
DATESINPERIOD(DIADOSCE[Date], LASTDATE(DIADOSCE[Date]),7,DAY)
)

and 

7Day2 = 
AVERAGEX(
DATESINPERIOD(DIADOSCE[Date],LASTDATE(DIADOSCE[Date]),7,DAY),
(SUM(DIADOSCE[Eligible]) / COUNT(DIADOSCE[ID])))

Graph looks like this, but i cannot figure out the rolling 7 day eligible percentage.

[nshindel]

Hi

My data looks like this

ID          Date             Eliglble

ABC        9/1/2018      1

DEF        9/1/2018       0

HIJ         9/1/2018       1

LMN       9/2/2018       0

OPQ       9/2/2018       0

RST        9/2/2018       1

All the IDs are distinct. I am looking for a percentage of IDs that are eligible, over time. That has been solved simply with a new calculated measure 

PercentEligible = SUM(DIADOSCE[Eligible]) / COUNT(DIADOSCE[ID])

From there, I am trying to get a rolling 7 day average, but PBI doesnt seem to handle how to do that on a calculated field. I tried this

7DayAverage =
CALCULATE(
SUM(DIADOSCE[Eligible]) / COUNT(DIADOSCE[ID]),
DATESINPERIOD(DIADOSCE[Date], LASTDATE(DIADOSCE[Date]),7,DAY)
)
/
CALCULATE(
DISTINCTCOUNT(DIADOSCE[Date]),
DATESINPERIOD(DIADOSCE[Date], LASTDATE(DIADOSCE[Date]),7,DAY)
)

and 

7Day2 =
AVERAGEX(
DATESINPERIOD(DIADOSCE[Date],LASTDATE(DIADOSCE[Date]),7,DAY),
(SUM(DIADOSCE[Eligible]) / COUNT(DIADOSCE[ID])))

Graph looks like this, but i cannot figure out the rolling 7 day eligible percentage.

[v-frfei-msft]

Hi @nshindel,

Please check the sample as below. If it doesn't meet your requirement, kindly share your excepted result to me,

Measure = 
CALCULATE (
    SUM ( Table1[Eliglble] ),
    DATESINPERIOD ( Table1[Date ], MAX ( Table1[Date ] ), -7, DAY )
)
    / CALCULATE (
        COUNTROWS ( Table1 ),
        DATESINPERIOD ( Table1[Date ], MAX ( Table1[Date ] ), -7, DAY )
    )

For more details, please check the pbix as attached.

Regards,

Frank

[v-frfei-msft]

Hi @nshindel,

Does that make sense? If so, kindly mark the answer as solution to close the case.

Regards,
Frank

[LivioLanzo · Accepted solution]

try adding a Dimension date table and link it to your data table on 1 to many relationships

then try this measure

=
AVERAGEX (
    DATESINPERIOD (
        Dates[Date],
        MAX ( Dates[Date] ),
        -7,
        DAY
    ),
    CALCULATE (
        DIVIDE (
            SUM ( DIADOSCE[ Eliglble] ),
            COUNTROWS ( DIADOSCE )
        )
    )
)