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


     

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