Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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 PRODUCTI...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    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's avatar
Anonymous
Not applicable
9 years ago

No :-(

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      Can i also use the Facility and Product filters ?

       

      I have a goal for each client - product - facillity combination :-)

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        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