Forum Discussion

jayasurya_prud's avatar
jayasurya_prud
Advocate III
3 years ago
Solved

Date Year Functions in DAX Issue

Hi!  I am working on a Dax to have a year filter dynamically.    I have two columns to consider for Date 1. Year_String = Example Value = "2022 - 23". Data Type Strimg 2. Year_Num  = Example Val...
Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi jayasurya_prud ,

     

    Are table and table the same table? There is no [no of grad] in the sample data, and I try to make you understand how to dynamically group calculations.

    Since you want dynamic results. If it's a calculated column, please try

    year column = 
var counts = CALCULATE(COUNTROWS('Table'),FILTER('Table',[Year_String]=MAX('Table'[Year_String])))
var grads_attr =CALCULATE(COUNTROWS('Table'),FILTER('Table',[no of years] = 1&&[Not Active]<>"Yes"&&[Year_String] =MAX('Table'[Year_String])))
return counts - grads_attr

    If it's a measure, please try

    year measure = 
var counts = CALCULATE(COUNTROWS('Table'),FILTER(ALLSELECTED('Table'),[Year_String]=MAX('Table'[Year_String])))
var grads_attr =CALCULATE(COUNTROWS('Table'),FILTER(ALLSELECTED('Table'),[no of years] = 1&&[Not Active]<>"Yes"&&[Year_String] =MAX('Table'[Year_String])))
return counts-grads_attr

    We often use FILTER(ALLSELECTED('Table'),[Year_String]=MAX('Table'[Year_String]) to group in measures and use FILTER('Table',[Year_String]=EARLIER('Table'[Year_String]) to group in calcualted columns.

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi jayasurya_prud ,

 

You can create a measure as 

Expected = var _presentyear=YEAR(TODAY())
return _presentyear-SUM('Table'[Year_Num])

If you want to use Year_String column to calculate, please refer to this measure.

Expected2 = var _presentyear=YEAR(TODAY())
var _year=VALUE(LEFT(MAX('Table'[Year_String]),4))
return _presentyear-_year

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

  • jayasurya_prud's avatar
    jayasurya_prud
    Advocate III
    3 years ago

    Hi! Apprecoat the reply. Thanks for the dax. But this is not helping me to use the date function in countrows filter.  Please find the detail below.

     

    Hi! Thanks for replying with the dax. I am afraid that this is not working. Here is the sample data

     

    S_IDYear_StringYear_numno of yearsGradNot Active
    12022 - 2320221yesYes
    22021 - 2220212nono
    32020 - 2120203yesYes
    42019 - 2020194nono
    52018 - 1920185yesYes
    62017 - 1820176nono
    72022 - 2320221yesYes
    82021 - 2220212nono
    92020 - 2120203yesYes
    102019 - 2020194nono
    112018 - 1920185yesYes
    122017 - 1820176nono
    132022 - 2320221yesYes
    142021 - 2220212nono
    152020 - 2120203yesYes
    162019 - 2020194nono
    172018 - 1920185yesYes
    182017 - 1820176nono
    192022 - 2320221yesYes
    202021 - 2220212nono
    212020 - 2120203yesYes
    222019 - 2020194nono
    232018 - 1920185yesYes
    242017 - 1820176nono
    252022 - 2320221yesYes
    262021 - 2220212nono
    272020 - 2120203yesYes
    282019 - 2020194nono
    292018 - 1920185yesYes
    302017 - 1820176nono
    312022 - 2320221yesYes
    322021 - 2220212nono
    332020 - 2120203yesYes
    342019 - 2020194nono
    352018 - 1920185yesYes
    362017 - 1820176nono
    372022 - 2320221yesYes
    382021 - 2220212nono
    392020 - 2120203yesYes
    402019 - 2020194nono
    412018 - 1920185yesYes
    422017 - 1820176nono
    432022 - 2320221yesYes
    442021 - 2220212nono
    452020 - 2120203yesYes
    462019 - 2020194nono
    472018 - 1920185yesYes
    482017 - 1820176nono
    492022 - 2320221yesYes
    502021 - 2220212nono
    512020 - 2120203yesYes
    522019 - 2020194nono
    532018 - 1920185yesYes
    542017 - 1820176nono

     

     

    I want help of date function in the filter. This is the dax I am trying

     

    year_1 =
    var counts = CALCULATE(COUNTROWS(Table),Table[year_string] = "2022 - 23")
    var grads_attr = CALCULATE(COUNTROWS(table),table[no of grad] = 1,not(table[not active] in {"yes"}), table[year_string] = "2022 - 23")
    return counts - grads_attr

     
    this gives me the records of last year. But I have used the year value as Hard code value. I need change this to dynamic.
     
    like wise I am using this formula for many years, 
     
    year_2 =
    var counts = CALCULATE(COUNTROWS(Table),Table[year_string] = "2021 - 22")
    var grads_attr = CALCULATE(COUNTROWS(table),table[no of grad] = 1,not(table[not active] in {"yes"}), table[year_string] = "2021 - 22")
    return counts - grads_attr
     
    year_3 =
    var counts = CALCULATE(COUNTROWS(Table),Table[year_string] = "2020 - 21")
    var grads_attr = CALCULATE(COUNTROWS(table),table[no of grad] = 1,not(table[not active] in {"yes"}), table[year_string] = "2020 - 21")
    return counts - grads_attr
     
    year_4 =
    var counts = CALCULATE(COUNTROWS(Table),Table[year_string] = "2019 - 20")
    var grads_attr = CALCULATE(COUNTROWS(table),table[no of grad] = 1,not(table[not active] in {"yes"}), table[year_string] = "2019 - 20")
    return counts - grads_attr
     
    year_5 =
    var counts = CALCULATE(COUNTROWS(Table),Table[year_string] = "2018 - 19")
    var grads_attr = CALCULATE(COUNTROWS(table),table[no of grad] = 1,not(table[not active] in {"yes"}), table[year_string] = "2018 - 19")
    return counts - grads_attr
     
    year_6 =
    var counts = CALCULATE(COUNTROWS(Table),Table[year_string] = "2017 - 18")
    var grads_attr = CALCULATE(COUNTROWS(table),table[no of grad] = 1,not(table[not active] in {"yes"}), table[year_string] = "2017 - 18")
    return counts - grads_attr
     
     
    please help me with the dax.