Forum Discussion
COVERAGE calculation between current and previous year
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 , 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])
2 Replies
- amitchandak
Super User
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])