cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
martin_alt Regular Visitor
Regular Visitor

Use today's date to filter a column

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 :

 

ITEMGOALPRODUCTIONDATEPRODUCTION TO DATEPROJECTION
REP2101/01/20171 
REP2102/01/20171 
REP2003/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 ? 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
v-huizhn-msft Super Contributor
Super Contributor

Re: Use today's date to filter a column

Hi @martin_alt,

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.

3.png

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

Thanks,
Angelia


 

View solution in original post

9 REPLIES 9
v-huizhn-msft Super Contributor
Super Contributor

Re: Use today's date to filter a column

Hi @martin_alt,

>>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

martin_alt Regular Visitor
Regular Visitor

Re: Use today's date to filter a column

>> 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 🙂

martin_alt Regular Visitor
Regular Visitor

Re: Use today's date to filter a column

Anyone on this ?

 

Is my answer clear enough ?

 

Thanks 🙂

v-huizhn-msft Super Contributor
Super Contributor

Re: Use today's date to filter a column

Hi @martin_alt

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

Thanks,
Angelia

Highlighted
martin_alt Regular Visitor
Regular Visitor

Re: Use today's date to filter a column

No 😞

martin_alt Regular Visitor
Regular Visitor

Re: Use today's date to filter a column

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 :

 

IDCLIENTPRODUCTFACILITYDATE
4387414520259906ETW68201/01/2017
4387414520339906ETW68201/01/2017
3187358341583425DEPOCW68202/01/2017
3187358336303425DEPOCW68202/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 :

 

CLIENTPRODUCTFACILITYDATEGOAL
9906ETW68201/01/20171
9906ETW68202/01/20171
9906ETW68203/01/20171
3425DEPOCW68201/01/20171
3425DEPOCW68202/01/20171
3425DEPOCW68203/01/20171

 

 

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 :

 

CLIENTPRODUCTFACILITYDATEGOALPRODUCTION
9906ETW68201/01/201712
9906ETW68202/01/201710
9906ETW68203/01/20171 
3425DEPOCW68201/01/201710
3425DEPOCW68202/01/201712
3425DEPOCW68203/01/20171 

 

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 :

 

CLIENTPRODUCTFACILITYDATEGOALPRODUCTIONPROJECTION
9906ETW68201/01/201712 
9906ETW68202/01/201710 
9906ETW68203/01/20171 1
3425DEPOCW68201/01/201710 
3425DEPOCW68202/01/201712 
3425DEPOCW68203/01/20171 1

 

I hope it's clear enough now 🙂

v-huizhn-msft Super Contributor
Super Contributor

Re: Use today's date to filter a column

Hi @martin_alt,

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.

3.png

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

Thanks,
Angelia


 

View solution in original post

martin_alt Regular Visitor
Regular Visitor

Re: Use today's date to filter a column

Can i also use the Facility and Product filters ?

 

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

v-huizhn-msft Super Contributor
Super Contributor

Re: Use today's date to filter a column

Hi @martin_alt,

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

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 168 members 1,735 guests
Please welcome our newest community members: