Forum Discussion
Daily Required Run rate
DimaMD Yes , where should I send you the sample data file? can I have your email address ?
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
DimaMD No problem ...Achieved spends is spends column in acheived sheet and it corresponds to "spend" column in Planned sheet
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
Hi Anonymous Do you have a planned amount of 200,000 for September?
DimaMD YES, do you want me to send you my data file ?
Anonymous Yes
DimaMD sharing my dymmy data with you please check
https://globalappsportal-my.sharepoint.com/:f:/g/personal/prajakta_keni_dentsu_com/Eqg95-48_1xLvywddqMSI2gBx8BHjFYxDLwB8mP8fmK0LA?e=pFmEMUDimaMD Sharing dymmy data with you please check
https://globalappsportal-my.sharepoint.com/:f:/g/personal/prajakta_keni_dentsu_com/Eqg95-48_1xLvywddqMSI2gBx8BHjFYxDLwB8mP8fmK0LA?e=pFmEMUAnonymous 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
DimaMD Yes ,
1.firstly I am getting acheived data Daily
2.and planned data Monthly,so first Query is to convert it to dailyDaily 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 understandingHi, Anonymous Sorry for the delay, how do you calculate "Archive spend"? and which column corresponds to the amount in the "Planned Data" table?
DimaMD Any luck on this ?
HI, Anonymous Currently, there are several ideas we are trying to implement.