Power BI, issue to sum last working day and last two working day

1
votes

I have a question on how to calculate the sum of the last previous working day.

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:

  • Sum for D (17/09/2020) -> 40
  • Sum for D-1 (16/09/2020) -> 10
  • Sum for D-2 (15/09/2020) -> blank

When I put myself at the date 18/09/2020, I should see this:

  • Sum for D (18/09/2020) -> 50
  • Sum for D-1 (17/09/2020) -> 40
  • Sum for D-2 (16/09/2020) -> 10

When I put myself at the date 21/09/2020, I should see this:

  • Sum for D (21/09/2020) -> 80
  • Sum for D-1 (18/09/2020) -> 50
  • Sum for D-2 (17/09/2020) -> 40

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,

3
powerbidaxpowerbi-desktopmeasure
Do you want those 3 value in 3 column for each row? or separate value to shown in CARD? – mkRabbani
I would like three separate values, D, D-1 and D-2. The idea would be at the end to be able to see the result of D-(D-1) in order to retreive the gap between the two values. – MarcLC
what if there is no previous date available? – mkRabbani
If it like for the D-2 of the date 17/09/20, it can display empty/blank it is not important – MarcLC
so both d-1 and d-2 for date 21/09/20 will be blank, right? – mkRabbani

3 Answers

0
votes

I strongly recommend to add a Date dimension to your model. That will make date-based analytics easier.

Here's my first attempt. I assumed only Saturdays and Sundays as holidays. If you need to consider other holidays, you'd need a Date dimension table. Also note that I used ALL(Data) inside the CALCULATE. This will remove any filters applied to the Data table.

Output = 
VAR _selectedDate =
    MAX ( 'Data'[Date] )
VAR _selectedDateDay =
    WEEKDAY ( _selectedDate )
VAR _selectedDateminus2 =
    SWITCH (
        TRUE,
        _selectedDateDay = 2, _selectedDate - 4,
        _selectedDateDay = 3, _selectedDate - 5,
        _selectedDate - 2
    )
RETURN
    CALCULATE (
        SUM ( 'Data'[Amount] ),
        FILTER (
            ALL ( 'Data' ),
            'Data'[Date] <= _selectedDate
                && 'Data'[Date] >= _selectedDateminus2
        )
    )

enter image description here

0
votes

Create these below 3 measures-

D_0 = 
CALCULATE(
    SUM('your_table_name'[Amount]),
    FILTER(
        ALL('your_table_name'),
        your_table_name[Date] = SELECTEDVALUE(your_table_name[Date])
    )
)

D_1 = 
CALCULATE(
    SUM('your_table_name'[Amount]),
    FILTER(
        ALL('your_table_name'),
        your_table_name[Date] = SELECTEDVALUE(your_table_name[Date]) - 1
    )
)

D_2 = 
CALCULATE(
    SUM('your_table_name'[Amount]),
    FILTER(
        ALL('your_table_name'),
        your_table_name[Date] = SELECTEDVALUE(your_table_name[Date]) -2
    )
)

Here is the output when you select date "18/09/20" from the slicer-

enter image description here

0
votes

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,