Here is my data set:
Date Name Amount
16/09/20 A 10
17/09/20 A 10
17/09/20 B 30
18/09/20 A 50
21/09/20 A 20
21/09/20 B 60
22/09/20 B 50
In my dashboard, I have a filter to choose a date of display and I see the sum of today; last previous working day and second last previous working day.
So when I put myself at the date 17/09/2020, I should see this:
When I put myself at the date 18/09/2020, I should see this:
When I put myself at the date 21/09/2020, I should see this:
I don't find a way to sum for a previous day using calculate or sum and previousday is not helpful in my case.
Thanks in advance for the help,
I just found a solution, here I post it:
For D:
wd = SUM(data[Amount])
For D-1:
lwd =
var ad = DATEADD(data[Date];0;DAY)
var lwd = IF(
WEEKDAY(ad) = 1; //sunday
ad - 2;
IF(
WEEKDAY(ad) = 2; //monday
ad - 3;
ad - 1 //others
)
)
var sumLWD = CALCULATE(SUM(data[Amount]);data[Date]=lwd)
return sumLWD
For D-2:
l2wd =
var ad = DATEADD(data[Date];0;DAY)
var lwd2 = IF(
WEEKDAY(ad) = 2; //monday
ad - 4;
IF(
WEEKDAY(ad) = 3; //tuesday
ad - 4;
ad - 2 //others
)
)
var sumLWD2 = CALCULATE(SUM(data[Amount]);data[Date]=lwd2)
return sumLWD2
Thanks all for your help and time. Regards,