Forum Discussion
Compare 4 years and count
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?
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. :smileyhappy:
measure = SUMX ( 'Clients'; [Lost2017] )
Regards
1 Reply
v-ljerr-msftMicrosoft Employee
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. :smileyhappy:
measure = SUMX ( 'Clients'; [Lost2017] )
Regards