Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to 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 )
)
)
)
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
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.
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
Hi @nshindel,
Does that make sense? If so, kindly mark the answer as solution to close the case.
Regards,
Frank
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 )
)
)
)
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
User | Count |
---|---|
92 | |
86 | |
66 | |
62 | |
58 |
User | Count |
---|---|
150 | |
113 | |
95 | |
80 | |
72 |