Daily Required Run rate

[Anonymous]

Hello ,
I want to add a calculated column of the daily  required run rate by the company. My data looks like below.
I have 2 tables ( 1 for acheived data and other for planned data ) to check the performance i need to show what is the daily runrate should be to acheive plannd value.
thanks in advance.

[DimaMD]

Hi @Anonymous If you have a plan for only one date, you can break it into days, try this event

VAR _plan = [_Plan margin]
VAR _2AmountDatesMount = DAY(EOMONTH(MIN('Календар'[Date]),0))
VAR _3PlanDates =DIVIDE(_Plan, _2AmountDatesMount)
VAR _4 = DATEDIFF( MIN('Dates'[Date]),MAX('Dates'[Date]),DAY)+1 
return
_3PlanDates*_4

[Anonymous]

I have  tried this measure it is reflecting only for first day of the month ...how can i get it daily ?

[DimaMD]

@Anonymous add a sliser with dates from the plan and from the actual (or date table)

[DimaMD]

@Anonymous You need to unlink the plan table from the date table

[Anonymous]

@DimaMD  values are coming incorrect now 

[DimaMD]

@Anonymous add a stacker with dates from the plan and from the actual (or date table)

[Anonymous]

@DimaMD  thank you for your reply i am taking date column in table visual from calendar date table and now spend values are  changing by date(actual table) slicer and planned values are changing by date(planned table) slicer whcih means table is not filtering only by one slicer 

[DimaMD]

@Anonymous that's right, my reports also use two date slices

[Anonymous]

@DimaMD  cant we acheive this by using one slicer ?

 plus in your report have you calculated daily runrate ?

if yes may I have pbix. file for the same 

[DimaMD]

@Anonymous Can you provide non-confidential sample data so I can test something?

[Anonymous]

@DimaMD  Yes , where should I send you the sample data file? can I have your email address ?

[DimaMD]

@Anonymous Hi, I have created my test data, please review the file and try to reproduce.
What i did
1 We transfer the plan to the main table (LOOKUPVALUE) - New colum

plan = LOOKUPVALUE('plan'[plan], plan[Data], 'data'[Dates] )

2 We calculate the number of days in a month (Number of days) - new colum

Number of days = DAY(EOMONTH(MIN('data'[Dates]),0))

3 We calculate what the plan will be for one day 

Plan_one_dates = DIVIDE([plan],[Number of days])

4 We allocate a plan for one day in the section of other dates 

plan_dates = MAXX(FILTER('data','data'[Dates] <= EARLIER([Dates])),[Plan_one_dates])

Result

[Anonymous]

@DimaMD Hii while creating column to transfer data to main table i m facing an issue 

[Anonymous]

@DimaMD sum planned is not matching as fir september planned total is 200000 so daily it should be 6666(200000/30) but in my case it is coming 3333 and end total is also not coming correct 

[Anonymous]

@DimaMD  YES, do you want me to send you my data file ?

[DimaMD]

@Anonymous Yes

[Anonymous]

@DimaMD Sharing dymmy data with you please check
https://globalappsportal-my.sharepoint.com/:f:/g/personal/prajakta_keni_dentsu_com/Eqg95-48_1xLvywdd... 

[DimaMD]

@Anonymous Thank you for providing the file, but could you explain how you do the calculations, because I don't understand how the amounts are planned, how you calculate them

[Anonymous]

@DimaMD  Yes ,
1.firstly I am getting acheived data Daily
2.and planned data Monthly,so first Query is to convert it to daily

Daily planned spends= monthly planned spends/no of days in month
(e.g. if my planned spends for oct 22 is 300000 so i want to show is daily 9677 (300000/no. of days in month).
3.once that is resolved i want to show daily required runrate formula for that is 
no of days left = end of the month day-current day
(if I select 11th of oct so no of days will be 20 i.e. 31st oct -11oct)
Daily runrate =(Daily planned spends-daily acheived spend)/no. of days left


Attatching an image for better understanding 

[DimaMD]

Hi, @Anonymous Sorry for the delay, how do you calculate "Archive spend"? and which column corresponds to the amount in the "Planned Data" table?