CODEWITHSUNDEEP
powerbidax
bmsqldev
bmsqldev

Reputation: 2735

POWER BI : Calculate the percentage excluding last 5 days

I am trying to compute the percentage of Free to Paid Sales conversions for a particular period.

enter image description here

Sales become paid after the threshold of 5 days. So Conversion % should exclude the last 5 days. Expected output is below.

enter image description here

Below are the measure I have created.

  1. FreeSales : SUM( DATA[Free_Trials])

  2. Conversions : SUM(DATA[Conversions])

  3. Conv % : Calculate ( DIVIDE( FreeSales/Conversions,0), DATESBETWEEN(DATA[DATE], STARTDATE, ENDDATE-5))

(P.S: STARTDATE & ENDDATE are the min & max values from the date slicer)

Conv % is not working properly . It giving same value for all the rows in the table. Please help to fix this issue.

Thanks in advance!

Upvotes: 0

Views: 245

Answers (1)

user8078111
user8078111

Reputation: 468

You can create a calculated column with the below DAX formula.

Column2 =
VAR C = MAX ( Sheet1[Date] )
VAR RESULT =
     IF (
         ( Sheet1[Date] ) = C,
         0,
           IF (
              Sheet1[Date] = C - 1,
              0,
                IF (
                  Sheet1[Date] = C - 2,
                  0,
                    IF (
                      Sheet1[Date] = C - 3,
                      0,
                        IF (
                          Sheet1[Date] = C - 4,
                          0,
                  CALCULATE ( DIVIDE ( SUM ( Sheet1[Paid Sales] ), SUM ( Sheet1[Free Sales] ) ) )
                )
            )
        )
    )
)
RETURN
    RESULT

Output

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Upvotes: 1

Related Questions