COVERAGE calculation between current and previous year

[Anonymous]

Hi.

Need help to create calculation for coverage.

Customer need to check if item has been sold this year and last year and then we have coverage.

If the item has not been sold either this year and last year ther will not be coverage (see exampel below)

[Anonymous]

Many thanks - works perfect and is quick

[amitchandak · Accepted solution]

@Anonymous , Assume you already have measure like first 2.

This Year = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last Year = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

the other measures you need

coverage = if(not(isblank([This Year])) && not(isblank([Last Year])), 1, blank())

coverage total = sumx(addcolumns(summarize(Table, Table[Customer], Table[Item]), "_1", [coverage]), [_1])

Total = countrows(summarize(Table, Table[Customer], Table[Item]))

coverage % = divide([coverage total],[Total])