Reply
Frequent Visitor
Posts: 9
Registered: ‎12-13-2016
Accepted Solution

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

 

1. Add a NumberOfDays column in your target table.

 

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

 

7.PNG

 

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

 

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

88.PNG

 

 

Regards,

View solution in original post


All Replies
Super Contributor
Posts: 877
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...

 

Any question please feel free to ask.

 

Regards,

Mfelix

 

 

Moderator
Posts: 2,512
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.

 

1. Add a NumberOfDays column in your target table.

 

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

 

7.PNG

 

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

 

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

88.PNG

 

 

Regards,