Count of Errors which don't exist in current period but existed in last period

[Anonymous]

Data: I have 1 table with item column which list records with errors, and another which has the violation date.

Error Log

Violation Id     DimViolationDate

1	26/06/2018
2	16/06/2018
3	16/06/2018
1	16/06/2018

Requirement: I want to find out number of errors which have been fixed since last time period(i.e from 16/06/2018 to 26/06/2018). In this case it would 2 as they show up in past period(16/06/2018) but not in current period(26/06/2018).

In the above case the result would be 2 because Violation IDs 2 & 3 show up in past period(16/06/2018) but not in current period(26/06/2018)

Steps I took: 

1) Created a calculated table with data from current & last period

Current&PastItems = 
VAR CurrentPeriod = CALCULATE(MAX(FactName[DimViolationDate]),FILTER(FactName,FactName[DimViolationDate]=MAX(FactName[DimViolationDate])))
VAR LastPeriod = CALCULATE(MAX(FactName[DimViolationDate]),FILTER(FactName,FactName[DimViolationDate]<>MAX(FactName[DimViolationDate])))
RETURN FILTER(ALL(FactName[Violation Id],FactName[DimViolationDate]),OR(FactName[DimViolationDate]=LastPeriod,FactName[DimViolationDate]=CurrentPeriod))

2) Calculated a field saying if it exists in last period & another if it exists in current period

Exists in Current Period = 
VAR CurrentPeriod = CALCULATE(MAX('Current&PastItems'[DimViolationDateKey]),FILTER('Current&PastItems','Current&PastItems'[DimViolationDateKey]=MAX('Current&PastItems'[DimViolationDateKey])))
VAR OccuranceInPeriod = CALCULATE(DISTINCTCOUNT('Current&PastItems'[Violation Id]),('Current&PastItems'[Violation Id]<>BLANK()),

FILTER('Current&PastItems','Current&PastItems'[DimViolationDateKey] = CurrentPeriod))+0
RETURN IF(OccuranceInPeriod>=1,"Yes","No")

Exists in Last Period = 
VAR LastPeriod = CALCULATE(MAX('Current&PastItems'[DimViolationDate]),FILTER('Current&PastItems','Current&PastItems'[DimViolationDate]<>MAX('Current&PastItems'[DimViolationDate])))
VAR OccuranceInPeriod = CALCULATE(DISTINCTCOUNT('Current&PastItems'[Violation Id]),('Current&PastItems'[Violation Id]<>BLANK()),ALLEXCEPT('Current&PastItems','Current&PastItems'[Violation Id]),
FILTER('Current&PastItems','Current&PastItems'[DimViolationDate] = LastPeriod))+0
RETURN IF(OccuranceInPeriod>=1,"Yes","No")

3) Count items which have a yes for last period and no for current period & number of occurances = 1

Number of Occurances = CALCULATE(DISTINCTCOUNT('Current&PastItems'[DimViolationDateKey]),ALLEXCEPT('Current&PastItems','Current&PastItems'[Violation Id]))

Number of Fixed Violations = CALCULATE(DISTINCTCOUNT('Current&PastItems'[Violation Id]),FILTER('Current&PastItems','Current&PastItems'[Exists in Last Period]="Yes" && 'Current&PastItems'[Exists in Current Period]="No" && [Number of Occurances]=1) ) + 0

[v-juanli-msft · Accepted solution]

Hi @Anonymous

Create calculated columns

cuurrent = MAX([DimViolationDate])

maxdate of each id = CALCULATE(MAX([DimViolationDate]),ALLEXCEPT(FactName,FactName[Violation Id]))

fixed untill current = IF([cuurrent]=[maxdate of each id],1,0)

fixed previous = CALCULATE(DISTINCTCOUNT(FactName[Violation Id]),FILTER(ALL(FactName),[fixed untill current]=0))

Best Regards

Maggie

[Anonymous]

Hi @v-juanli-msft,

Could you please explain what "fixed previous" calculation does?

[v-juanli-msft]

Hi @Anonymous

If current date is 2018/6/26, there are two errors were fixed before 2018/6/26, (2->2018/6/16, 3->2018/6/16)

the error 1 is fixed at 2018/1/16, then this error happened again and is finally fixed in 2018/1/26.

Thus, "fixed previous"  means:

Count of Errors which don't exist in current period but existed in last period

Best Regards

Maggie

[v-juanli-msft]

Hi @Anonymous

Thank you for such detailed steps you applied.

However, it is difficult for me to understand your dataset.

As your requirement said, from the "Error Log", I can't find anything about "ast time period","current period" and detemine which show up in past period but not in current period.

I would like to suggest you give an example of your tables(maybe not the real data, just can explain your scenario), relationships between these tables and expected result.

Best Regards

Maggie

[Anonymous]

@v-juanli-msft I have updated the information to provide the required details.

[v-juanli-msft]

Hi @Anonymous

Please look at the article about How-to-Get-Your-Question-Answered-Quickly.

Best Regards

Maggie

[Anonymous]

@v-juanli-msft I appreciate the link, but not sure why you are referring me to that link? Is there something I messed up while posting my query. I seem to have followed everything from that link.