Projecting future sales and revenue

[leo8nardo8]

Hello,
I'm struggling in how to make some analysis in Power Bi.
I'm comfortable in manipulate and represent with Power Bi, but I'm kinda newbie in more complex DAX solutions.

So, my goal is to analyse future payments and project future sales/payments coming from renovations.
Examples bellow.

• Client 1 have buyed offer AA2 from product A at 20/11
  
  • I will receive in 12 installments (col Payment received)
  • And in 365D (Renovation) they will charged again with the Renovation price
• Client 2 have buyed offer BB2 from product B at 22/11
  • I will receive in 30D (Payment received)
  • And in 30D (Renovation) they will charged again with the Renovation price
    
    • This will occur several time along the year

Some examples of the data:

Products

Product	Offer	Initial price	Renovation (days)	Renovation price
A	AA1	750	180	1000
A	AA2	1500	365	2000
B	BB1	10	30	30
B	BB2	50	90	90
B	BB3	100	180	180

Buyers

Name	Offer	Purchase date	Payment received
Client 1	AA2	20/11/2020	12x Installments Credit Card
Client 1	BB1	21/11/2020	30D
Client 2	BB2	22/11/2020	30D
Client 3	BB3	23/11/2020	30D

So I want to calculate the revenue over all 2021 from payments and projected sales renovations by products.
But atm I don't have ideas on how to start this at Power Bi in a practical way.
ideas?

[v-yuaj-msft · Accepted solution]

Hi @leo8nardo8 

Based on your description, you can do some steps as follows.

1. Create a ‘End Date’ column.

End date =

var x1=SWITCH('Buyers'[Payment received],"12x Installments Credit Card",365,"30D",30)

var x2=IF(x1>RELATED(Products[Renovation (days)]),x1,RELATED(Products[Renovation (days)]))

return

[Purchase date]+x2

1. Create two measures.

End_date = MAXX(FILTER(ALL(Buyers),[Name]=SELECTEDVALUE(Buyers[Name])),[End date])

Total revenue in 2021 = SUMX(FILTER(ALL('Buyers'),[Name]=SELECTEDVALUE(Buyers[Name])&&YEAR([End date])=2021),RELATED(Products[Initial price])+RELATED(Products[Renovation price]))

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

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