rolling average 7-day
1 Topic
7-day Moving Avg of filtered count of rows
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!Solved615Views0likes2Comments