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

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

    Anyone on this ?

     

    Is my answer clear enough ?

     

    Thanks :)

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

      Hi Anonymous，

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

      Thanks,
      Angelia

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        No :-(