## Desktop

Highlighted
Frequent Visitor
Posts: 9
Registered: ‎12-13-2016

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

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

Accepted Solutions
Moderator
Posts: 2,588
Registered: ‎03-06-2016

## Re: Target calculation across two tables. Revenue cumulative daily. Target revenue Monthly

@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.

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

All Replies
Super User
Posts: 1,277
Registered: ‎09-19-2016

## Re: Target calculation across two tables. Revenue cumulative daily. Target revenue Monthly

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...

Regards,

Mfelix

Proud to be a Datanaut!

Moderator
Posts: 2,588
Registered: ‎03-06-2016

## Re: Target calculation across two tables. Revenue cumulative daily. Target revenue Monthly

@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.

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