Compare 4 years and count

[LucianaDomene]

Hello, I need some help to create a report thats shows the sales for the last 4 years and then compare and count all of them to show all clients that buy in 2014, 2015 and 2016, but still don't in 2017.

I created the metrics:

QtdeLiq14 = CALCULATE(SUM('Faturamento 14 - 17'[Qtde liq]);DATESBETWEEN(Dim_Datas[data].[Date];[First2014];[Last2014]))

QtdeLiq15 = CALCULATE(SUM('Faturamento 14 - 17'[Qtde liq]);DATESBETWEEN(Dim_Datas[data].[Date];[First2015];[Last2015]))

QtdeLiq16 = CALCULATE(SUM('Faturamento 14 - 17'[Qtde liq]);DATESBETWEEN(Dim_Datas[data].[Date];[First2016];[Last2016]))

QtdeLiq17 = CALCULATE(SUM('Faturamento 14 - 17'[Qtde liq]);DATESBETWEEN(Dim_Datas[data].[Date];[First2017];[Last2017]))

Lost2017 = IF(([QtdeLiq17]<=0 || ISBLANK([QtdeLiq17])) && [QtdeLiq16]>0 && [QtdeLiq15]>0 && [QtdeLiq14]>0;1)

If I use the metric Lost2017 as a column in the report, I can easily ordenate by that metric. But I don't want to show that column, I want to use that as a filter, to show only the values = 1, and then rank by another column. And, It is not possible to COUNT that metric, I wish to do that too.

I tried to create Lost2017 as a column instead a metric, but the result isn't right, because I have a lot of columns in my table (date, product, region, etc...)

Can someone help me with that?

[v-ljerr-msft · Accepted solution]

Hi @LucianaDomene,

---

If I use the metric Lost2017 as a column in the report, I can easily ordenate by that metric. But I don't want to show that column, I want to use that as a filter, to show only the values = 1, and then rank by another column.

---

If I understand it correctly, you should be able to use the measure Lost2017 to apply a visual level filter on each visual you want to filter on the report.

 And, It is not possible to COUNT that metric, I wish to do that too.

---

I assume you have a Client dim table called Clients, and it is related to the 'Faturamento 14 - 17' table with the ClentID column. Then you should be able to use the formula below to create a new measure to count all clients that buy in 2014, 2015 and 2016, but still don't in 2017. 

measure = SUMX ( 'Clients'; [Lost2017] )

Regards