Target calculation across two tables. Revenue cumulative daily. Target revenue Monthly

[dinovic]

Table1. Cumulative Daily Revenue figures per Product

Product / revenue / date

Bike / 500 / 2017-01-01
Bike / 1000 / 2017-01-02
Car / 2000 / 2017-01-01
Car / 7000 / 2017-01-02

Table2. Monthly Revenue target per Product

Product / Revenue Target / Date (target)
Bike / 50000 / 2017-01-31
Car / 200000 / 2017-01-31

So, I would like to create a measure that calculates whether the Revenue from a particular product is on target, or not. The target would be calculated linearly, i.e. the revenue target for product Bike on the second of January would be 50000/31*2 = 3225.8. From Table1. we get the revenue on the 2nd of Januar was 1000, or only 31% of the targeted revenue that day.

How would you create this cross table calculation and/or the relationship between table 1 and 2, so it works for a selecter that selects based on product from table 1.

Thanks

[v-sihou-msft · Accepted solution]

@dinovic

In this scenario, you can need to add a column to show number of days in corresponding month. Then use this column calculate the cumulative target in your Revenue Table.

1. Add a NumberOfDays column in your target table.

NumberOfDays = DAY(ENDOFMONTH(Target[Date]))

2. Then you can add a cumulative target column in your Revenue table

Cumulative Target = (RELATED(Target[RevenueTarget])/RELATED(Target[NumberOfDays])*DAY(Revenue[Date]))

Regards,

[MFelix]

Hi @dinovic,

checks this post I believe it's similar to what you want.

https://community.powerbi.com/t5/Desktop/Cumulative-Sales-vs-Sales-Forecast-in-current-Month/m-p/160...

Any question please feel free to ask.

Regards,

Mfelix