7-day Moving Avg of filtered count of rows

[Anonymous]

Hi Dear Community,

I am trying to create a measure i PBI. I ave a date table which has 1:* relationship with my fact table, where I have my Result filed with text values in it ("Failed", "OK", etc.).

I have a measure where I calculated the number of "Failed" results: 

Count Nok = CALCULATE ( COUNTROWS ('alr ALR_PCI_SNIFFER_v2_DAILY_LOGS'), 'alr ALR_PCI_SNIFFER_v2_DAILY_LOGS'[Result] = "Failed")

 Now trying to calculate the average of that count with 7 days rolling window.

7-day Moving Avg NOK Results =

CALCULATE([Count Nok],

DATESINPERIOD('Date Table'[Date],

LASTDATE('Date Table'[Date]), -7, DAY

)

) /

CALCULATE(DISTINCTCOUNT('Date Table'[Day]),

DATESINPERIOD('Date Table'[Date],

LASTDATE('Date Table'[Date]), -7, DAY

)

)

My measure returns (BLANK). Whearas my meausre f filtered count of the ows gives numbers. So the problem is in the measure where I count the 7-dy rolling average of my first measure.

Can you help me to tackle this issue?

Thank you in advance!

[amitchandak · Accepted solution]

@Anonymous , Try like

CALCULATE(averagex(Value('Date Table'[Date), [Count Nok]),
DATESINPERIOD('Date Table'[Date],
LASTDATE('Date Table'[Date]), -7, DAY
)

Make sure datetable is marked as Date table and in filters and visual you are using period/date from date table 

[Anonymous]

Thank you! It worked!