topic Re: Daily Required Run rate in DAX Commands and Tips

Daily Required Run rate

Anonymous — Mon, 12 Dec 2022 07:01:23 GMT

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.

Re: Daily Required Run rate

tamerj1 — Mon, 12 Dec 2022 07:38:57 GMT

Hi Anonymous
Are the two tables connect with a relatioship? If so, what type of relationship and based on whih column? Do you have a date table? Can you please provide sample of the source data along with the expected result? The calculated column need to be created in which table?

Re: Daily Required Run rate

DimaMD — Mon, 12 Dec 2022 07:48:43 GMT

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

Re: Daily Required Run rate

Anonymous — Mon, 12 Dec 2022 08:55:55 GMT

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

Re: Daily Required Run rate

Anonymous — Mon, 12 Dec 2022 08:58:10 GMT

hello thank you for your reply firstlt there is no relation between twi tables except date column and I am getting achived values on DAILY basis and planned values on MONTHLY basis attatching both the data tables along with the desired results

Re: Daily Required Run rate

DimaMD — Mon, 12 Dec 2022 09:12:31 GMT

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

Re: Daily Required Run rate

Anonymous — Mon, 12 Dec 2022 09:13:07 GMT

DimaMD values are coming incorrect now

Re: Daily Required Run rate

DimaMD — Mon, 12 Dec 2022 09:16:15 GMT

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

Re: Daily Required Run rate

DimaMD — Mon, 12 Dec 2022 09:18:31 GMT

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

Re: Daily Required Run rate

Anonymous — Mon, 12 Dec 2022 09:49:53 GMT

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

Re: Daily Required Run rate

DimaMD — Mon, 12 Dec 2022 09:53:31 GMT

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

Re: Daily Required Run rate

Anonymous — Mon, 12 Dec 2022 10:00:25 GMT

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

Re: Daily Required Run rate

DimaMD — Mon, 12 Dec 2022 10:22:06 GMT

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

Re: Daily Required Run rate

Anonymous — Mon, 12 Dec 2022 10:33:30 GMT

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

Re: Daily Required Run rate

DimaMD — Mon, 12 Dec 2022 12:36:45 GMT

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

Re: Daily Required Run rate

Anonymous — Tue, 13 Dec 2022 05:07:55 GMT

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

Re: Daily Required Run rate

Anonymous — Tue, 13 Dec 2022 06:18:40 GMT

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

Re: Daily Required Run rate

DimaMD — Tue, 13 Dec 2022 07:20:48 GMT

Hi Anonymous Do you have a planned amount of 200,000 for September?

Re: Daily Required Run rate

Anonymous — Tue, 13 Dec 2022 07:30:06 GMT

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

Re: Daily Required Run rate

DimaMD — Tue, 13 Dec 2022 10:08:27 GMT

Anonymous Yes