hello everyone,
below is my sample data
date customer amount
06/12/2019 customer 1 200
22/01/2020 customer 2 250
15/02/2020 customer 3 300
10/03/2020 customer 1 100
29/04/2020 customer 2 220
i want to calculate sum of amount as two different columns based on difference of days between the starting date and ending date in the date slicer.
like this, (assume that the starting and ending dates are min and max date in date column)
date customer amount dateDiff lesser than 90 days greater than 90 days
06/12/2019 customer 1 200 145 200
22/01/2020 customer 2 250 98 250
15/02/2020 customer 3 300 74 300
10/03/2020 customer 1 100 50 100
29/04/2020 customer 2 220 0 220
total 1070 620 450
I achieved this by using measures but i don't want these columns in my table visual, i want only the last two columns.
when i remove other columns, it wil be like this
lesser than 90 days greater than 90 days
0 1070
because, measure retuns a single scalar value, if i remove other columns, the datediff measure will return 145 as difference
between starting and ending date and it will return sum of amount 1070 in column greater than 90 days (because 145 is greater than 90)
i need sum of amount as 620 in column lesser than 90 days and 450 in column greater than 90 days.
thanks in advance
