cancel
Showing results for
Did you mean:
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 :

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

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

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.

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

Thanks,
Angelia

9 REPLIES 9
Microsoft

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

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 🙂

Regular Visitor

Re: Use today's date to filter a column

Anyone on this ?

Is my answer clear enough ?

Thanks 🙂

Microsoft

Hi @martin_alt

Thanks,
Angelia

Highlighted
Regular Visitor

No 😞

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 :

 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 🙂

Microsoft

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.

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

Thanks,
Angelia

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 🙂

Microsoft

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

Announcements

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors