Search code examples
powerbidaxpowerbi-desktopmeasure

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



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,


Solution

  • 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,