Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    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])

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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])