Use today's date to filter a column

[Anonymous]

Hi guys,

I have a table with four columns :

ITEM / GOAL / PRODUCTION / DATE /

My date values are going from 01/01/2017 to 31/12/2017, there is a GOAL value for every line but the PRODUCTION column shows, of course,  zero for the forthcoming days.

I would like to create a PRODUCTION TO DATE column that is empty if the DATEis bigger than today's date and a PROJECTION column wich will be the ratio between my PRODUCTION TO DATE and GOAL LINE.

For example :

ITEM	GOAL	PRODUCTION	DATE	PRODUCTION TO DATE	PROJECTION
REP	2	1	01/01/2017	1
REP	2	1	02/01/2017	1
REP	2	0	03/01/2017		1

If today is the 03/01/2017 i want my projection column to show 1 because my past PRODUCTION is half my GOAL.

Would you have something for me ? 🙂

[v-huizhn-msft]

Hi @Anonymous,

>>I would like to create a PRODUCTION TO DATE column that is empty if the DATEis bigger than today's date

Please create a calculated column using the formula.

PRODUCTION TO DATE = IF(Table1[DATE]<TODAY(),1,BLANK())

>>a PROJECTION column wich will be the ratio between my PRODUCTION TO DATE and GOAL LINE.

It‘s confusing, how get 1? Please share more details for further analysis.

Thanks,
Angelia

[Anonymous]

>> I have a circular dependency detected... I think it's because my DATE is used on the request that creates my TABLE (it's a crossjoin table).

>> My projection is 1 because my until today PRODUCTION / GOAL ratio is 50% and my goal for tomorrow is 2 si I can predict that, if i'm producing at the same rythm, my production will be one 🙂

[Anonymous]

Anyone on this ?

Is my answer clear enough ?

Thanks 🙂

[v-huizhn-msft]

Hi @Anonymous，

You have resolved your issue by yourself? Please mark your workaround as answer. 

Thanks,
Angelia

[Anonymous]

No 😞

[Anonymous]

I will try to be more precise so you may be able to help 🙂

I have a production table with the list of the items producted with a identification number, a date, and a few other caracteristics, this tables looks like this :

ID	CLIENT	PRODUCT	FACILITY	DATE
438741452025	9906	ETW	682	01/01/2017
438741452033	9906	ETW	682	01/01/2017
318735834158	3425	DEPOCW	682	02/01/2017
318735833630	3425	DEPOCW	682	02/01/2017

And i have a goal table where i have for every day of the year, client, product and facility, a goal, it looks like this :

CLIENT	PRODUCT	FACILITY	DATE	GOAL
9906	ETW	682	01/01/2017	1
9906	ETW	682	02/01/2017	1
9906	ETW	682	03/01/2017	1
3425	DEPOCW	682	01/01/2017	1
3425	DEPOCW	682	02/01/2017	1
3425	DEPOCW	682	03/01/2017	1

In this last table, i have created a production column that count the number of line in my production table, so the goal table now looks like this :

CLIENT	PRODUCT	FACILITY	DATE	GOAL	PRODUCTION
9906	ETW	682	01/01/2017	1	2
9906	ETW	682	02/01/2017	1	0
9906	ETW	682	03/01/2017	1
3425	DEPOCW	682	01/01/2017	1	0
3425	DEPOCW	682	02/01/2017	1	2
3425	DEPOCW	682	03/01/2017	1

What i would like to do is to create a projection column that would calculate for the forthcoming days, a projection of my production based on my past production rythm.

In this case, if we assume that we are today the 03/01/2017 i would like to be able to show in my 03/01/2017 lines the projection 1, because my goal from 01/01/2017 to 02/01/2017 was to produce 2 units, i have produced 2 units, so my production rythm is 100% of my goal. And because my goal today is 1 i can assume that i will produce 1 item. So the final table should look like this :

CLIENT	PRODUCT	FACILITY	DATE	GOAL	PRODUCTION	PROJECTION
9906	ETW	682	01/01/2017	1	2
9906	ETW	682	02/01/2017	1	0
9906	ETW	682	03/01/2017	1		1
3425	DEPOCW	682	01/01/2017	1	0
3425	DEPOCW	682	02/01/2017	1	2
3425	DEPOCW	682	03/01/2017	1		1

I hope it's clear enough now 🙂

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,

I really understand your requirement. Please create a calculated column using the following formula.

PROJECTION = IF(GoalTable[DATE]= CALCULATE(MAX(GoalTable[DATE]),ALLEXCEPT(GoalTable,GoalTable[CLIENT])),DIVIDE(CALCULATE(MAX(GoalTable[PRODUCTION]),ALLEXCEPT(GoalTable,GoalTable[CLIENT])),CALCULATE( COUNTAX(FILTER(GoalTable,GoalTable[DATE]<Today()),GoalTable[CLIENT]),ALLEXCEPT(GoalTable,GoalTable[CLIENT]))),BLANK())

Then I explain the formula step by step.

1. You need need to count the CLIENT where the date is littler than Today.

Column 2 = CALCULATE( COUNTAX(FILTER(GoalTable,GoalTable[DATE]<DATE(2017,3,1)),GoalTable[CLIENT]),ALLEXCEPT(GoalTable,GoalTable[CLIENT]))

2. Then you need to get the PRODUCTION you have did.

Column 3 = CALCULATE(MAX(GoalTable[PRODUCTION]),ALLEXCEPT(GoalTable,GoalTable[CLIENT]))

3. Then you can calculate the production rythm based on the two calcualte dcolumns. But you want to display the result in the last row for every CLIENT, so I use the IF function to contral the result. I test using 2017/3/1 , and get expected result as follows.



Please reproduce based on the steps using your real data. You can test and debug step by step.

Thanks,
Angelia

[Anonymous]

Can i also use the Facility and Product filters ?

I have a goal for each client - product - facillity combination 🙂

[v-huizhn-msft]

Hi @Anonymous,

You mean use Facility and Product in ALLEXCEPT(GoalTable,GoalTable[CLIENT]), right? Definitely you can use Facility and Product, which depands on your requirement. If you have resolved your problem, please mark the right reply as answer.

Thanks,
Angelia