Forum Discussion
Rolling 7 day average from calculated field
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.
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 )
)
)
)
4 Replies
- LivioLanzo
Solution Sage
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 )
)
)
) 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-msftCommunity Support
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