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.           

     

amitchandak's avatar
amitchandak
Super User
3 years ago

jayasurya_prud , You can have column

 

max(Table[Year_num]) - [Year_num]

 

or a meausre

Maxx(allselected(Table),Table[Year_num]) - max(Table[Year_num]) 

 

 

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • jayasurya_prud's avatar
    jayasurya_prud
    Advocate III
    3 years ago

    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.
    • Anonymous's avatar
      Anonymous
      Not applicable
      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.