Using a Date filter and KPI

[Kornholio]

Hi,

I have a date, "Target Date", which has multiple date entries. I have it in a filter by "Relative Date" selections, for things like, weekly, bi weekly, etc. I also have an "Expectations" column, this is a number like "60" for specific Persons last name. 

So, right now a Person has an Expectation of 60 for weekly. If i choose in the Target Date filter Last Week, then the number of units works and the Expectation works, but when i choose Last Month, the number of units counts for all last month Target Date entries. 

What I need to do, is if i only have one Expectation for weekly, e.g. 60, then if I choose bi weekly, it should show Expectation of 120, then if i choose This Month, it should show 240, and so on.

Is there a way to apply the additions of Expectations for each time chosen on the filtered Target Date counts?

e.g I choose this week for chart, showing all Last Names and thier total units and Expectations, then i choose this month in the filter, i want the Expectations to multiply from beginning weekly Expectation (week 1=60, week 2=120, week 3= 180, etc)

[amitchandak]

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[Kornholio]

i am not sure how to paste a excel file

[Kornholio]

[Kornholio]

i am not sure how to post excel example

[Kornholio]

So, i have a date table and a column with Expectations(=20,30,40,50, etc.) So a line could look like this

Name, Location, Task, Date,  Expectation

John, Minnesota, Nurse, Jan 2020, 60

Basically, I wanted to pick a date range (1 week, 2 weeks, 3 weeks, etc), then say the 60 above is =1 week. If i choose 2 weeks, the number would multiply to 120 (or 60x2). If i choose 3 weeks, the number would report for Expectation as 180. Each week I choose from week 1 would multiply that way

[v-jayw-msft]

Hi @Kornholio ,

Sorry I didn't get the point.

How do you define the date range? Weeks between selected max [Target date] and min [Target date]?

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kornholio]

Alright, bit of a change, so if i have a Period Date filter dropdown and i need for Period 1 to show # of Units as whatever they have, say 30, and the Expectation is set for 60, then when i choose Period 1 and Period 2, the  # of units will double and the Expecation would too. Is there a calc for this? I will send a sheet of data i use if you need it

[v-jayw-msft]

Hi @Kornholio ,

I didn't find "units" column in your sample data but i did find "Expecation" column.

So basically you want the value/sum of "Expecation" multiplied by the count of selected "Period"? And should the target date within the selected "Period"?

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kornholio]

Hi,

So each CSV file that i pull in will have been called the name of the two week period. I have a calculation taking in the start and end dates as the period and use as a filter.

Looks like this

textperiod = CONCATENATE(CONCATENATE(CONCATENATE("Wk ",'Schedule Report'[Pay Period]),": "),CONCATENATE(CONCATENATE('Schedule Report'[startdate]," - "),'Schedule Report'[Enddate]))

and this is the calc for the Pay Period

Pay Period = FLOOR(DATEDIFF(DATE(2020,1,05),'Schedule Report'[Target Date],DAY)/14,1)+1

I am attaching another file(HTML) with units and expectations

Basically each day they have an expecation of 1.2 units and they have the # of units completed and Productivity Units.

I want to use the filter for Period and have the units accounted for in each period chosen and the Productivity Units.

[Kornholio]

also this is the calc for the start and end date

startdate = CALCULATE(

MIN('Schedule Report'[Target Date]),

ALLEXCEPT('Schedule Report','Schedule Report'[Pay Period])

Enddate = CALCULATE(

MAX('Schedule Report'[Target Date]),

ALLEXCEPT('Schedule Report','Schedule Report'[Pay Period])

)

[Kornholio]