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 Value  = 2022 . Data Type int.

 

In my dax, I need build the logic with the difference of present year (2023 - Year_Num ) = 1 (Example - 2023 - 2022 = 1)

 

It should iterate with differnt measures as 

 

(2023 - Year_Num ) = 2 (Example - 2023 - 2021 = 2)

(2023 - Year_Num ) = 3 (Example - 2023 - 2020 = 3)

(2023 - Year_Num ) = 4 (Example - 2023 - 2019 = 4)

(2023 - Year_Num ) = 5 (Example - 2023 - 2018 = 5)

(2023 - Year_Num ) = 6 (Example - 2023 - 2017 = 6)

 

I am even fine, if we can use the Year String column. 


Please help me with the Dax. 

I will be using this Dax as the filter in the Table. I will be creating multiple var in the DAX. So the Year filter should be going well with that.

 

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.           

     

5 Replies

  • 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.           

         

  • 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.