Forum Discussion

Irwin's avatar
Irwin
Icon for Helper IV rankHelper IV
4 years ago
Solved

Calculated column with "year" filter

Hi guys,

 

I have a table with some data that I would like to separate into a new calculated column.

I would like to create a column where I sumarize all the assignments that are 'Dynamic' and sort by year. 

Ie. in my little cut out from the data, that would mean 2 from 2022 and none from 2021.

 

However I cannot seem to create the correct filter that puts blank in 2021. Instead I just get "2" in all fields in my calculated column.

I use: Dynamic = CALCULATE(COUNTROWS('Table'), FILTER('Table', 'Table'[Assignment]="Dynamic"))

 

I have my table setup correctly with a date table. (I can easily make graphs etc where I use dates)

What filter/ekstra measure/etc do I need to do this?

 

Thank you very much for your help.

 

Assignment     Start date        Deadline

Static01-01-202131-03-2021
Static01-01-202131-03-2021
Static01-01-202131-03-2021
Static01-01-202130-06-2021
Static01-01-202130-06-2021
Static01-01-202230-06-2022
Static01-01-202230-06-2022
Static01-01-202230-06-2022
Static01-01-202230-06-2022
Static01-01-202230-06-2022
Static01-01-202230-09-2022
Static01-01-202230-09-2022
Static01-01-202231-12-2022
Static01-01-202231-12-2022
Static01-01-202231-12-2022
Static01-01-202231-12-2022
Static01-01-202231-12-2022
Dynamic01-01-2022 
Dynamic01-01-2022 



Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    Irwin Perhaps:

    Dynamic = IF([Assignment] = "Dynamic",CALCULATE(COUNTROWS('Table'), FILTER('Table', 'Table'[Assignment]="Dynamic")),BLANK())

3 Replies

  • Irwin's avatar
    Irwin
    Icon for Helper IV rankHelper IV
    4 years ago

    Guys, this was so simple I should have known.   Thanks a bunch for your help! It worked instantly 🙂 ❤️ 

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Irwin , You should create a measure like

     

    CALCULATE(COUNTROWS('Table'), FILTER('Table', 'Table'[Assignment]="Dynamic"))

     

    or

    a column like if('Table'[Assignment]="Dynamic" ,

    CALCULATE(COUNTROWS('Table'), FILTER('Table', 'Table'[Assignment]="Dynamic")), blank())

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Irwin Perhaps:

    Dynamic = IF([Assignment] = "Dynamic",CALCULATE(COUNTROWS('Table'), FILTER('Table', 'Table'[Assignment]="Dynamic")),BLANK())