Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to write dynamic years

I am new to Dax and have been trying to figure out how to display my data in dynamic years so I don’t have to update it every year. Below is the Dax that I can get to work for the current year, but when I add “Today ()-1”, it does not give me the results I need.

 

CAPITAL_2022 = IF(AND(GP_ALLOCATION_AND_FMSUMMARY_V[FUNDING_TYPE]="Capital",GP_ALLOCATION_AND_FMSUMMARY_V[YEAR_ID]=YEAR(TODAY())) ,GP_ALLOCATION_AND_FMSUMMARY_V[TOTAL_ALLOCATION],BLANK())

 

Here is my table details:

 

FUNDING TYPEYEAR IDTOTAL ALLOCATION 
Capital20240.00 
Capital20230.00 
Capital20216,849.00 
Capital202256,335.00 
    
    
Desired Outcome
2021 Capital2022 Capital2023 Capital2024 Capital
6,849.0056,335.000.000.00

 

thanks,

Debbie

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Anonymous ,

    According to your description, here's my solution.

    CAPITAL_2022 = 
MAXX (
    FILTER (
        ALL ( 'GP_ALLOCATION_AND_FMSUMMARY_V' ),
        'GP_ALLOCATION_AND_FMSUMMARY_V'[FUNDING TYPE] = "Capital"
            && 'GP_ALLOCATION_AND_FMSUMMARY_V'[YEAR ID] = YEAR ( TODAY () )
    ),
    'GP_ALLOCATION_AND_FMSUMMARY_V'[TOTAL ALLOCATION]
)

    CAPITAL_2021 = 
MAXX (
    FILTER (
        ALL ( 'GP_ALLOCATION_AND_FMSUMMARY_V' ),
        'GP_ALLOCATION_AND_FMSUMMARY_V'[FUNDING TYPE] = "Capital"
            && 'GP_ALLOCATION_AND_FMSUMMARY_V'[YEAR ID] = YEAR ( TODAY () )-1
    ),
    'GP_ALLOCATION_AND_FMSUMMARY_V'[TOTAL ALLOCATION]
)

    CAPITAL_2023 = 
MAXX (
    FILTER (
        ALL ( 'GP_ALLOCATION_AND_FMSUMMARY_V' ),
        'GP_ALLOCATION_AND_FMSUMMARY_V'[FUNDING TYPE] = "Capital"
            && 'GP_ALLOCATION_AND_FMSUMMARY_V'[YEAR ID] = YEAR ( TODAY () )+1
    ),
    'GP_ALLOCATION_AND_FMSUMMARY_V'[TOTAL ALLOCATION]
)
    CAPITAL_2024 = 
MAXX (
    FILTER (
        ALL ( 'GP_ALLOCATION_AND_FMSUMMARY_V' ),
        'GP_ALLOCATION_AND_FMSUMMARY_V'[FUNDING TYPE] = "Capital"
            && 'GP_ALLOCATION_AND_FMSUMMARY_V'[YEAR ID] = YEAR ( TODAY () )+2
    ),
    'GP_ALLOCATION_AND_FMSUMMARY_V'[TOTAL ALLOCATION]
)

     Get the expected result.

    I have a doubt, why not directly write [YEAR ID]="2021", [YEAR ID]="2022" etc, as when it comes to 2023, the result of the above formula is wrong, the CAPITAL_2023 should be [YEAR ID]=YEAR(TODAY()).

     

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

6 Replies

  • sevenhills's avatar
    sevenhills
    Super User
    4 years ago

    To answer, Formula has to be adjusted as ... (YEAR(TODAY()) + 1), (YEAR(TODAY()) - 1), (YEAR(TODAY()) + 2)

    - - - - - - - - - - - - - - - - - - - - - 

    But, I will suggest this way, as it is flexible 

    a) Say, this is your data table

     

    b) Go to data view

      add "New column"

     

     

    Display Header = [YEAR ID] & " " & TableFunding[FUNDING TYPE]

     

     

     Select the column "Display Header" , click column tools tab, sort by column and select "Year ID"

     

    In the end, the table look as 

     

     

    c) Report view, you can do many ways and one such way is

     

    c) 1) Data slicer is optional

    ~ Date slicer: to filter the years data

    c) 2) ~ For the table, you can do as slicer or you can filter in the filters 

     

     

     

    c) 3) one more way: Add new column usind DAX and use that one also 

     

     

    Capital = If ( TableFunding[FUNDING TYPE] = "Capital", TableFunding[TOTAL ALLOCATION], blank())

     

     

     

    Depending on  your needs ... 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you for the help. I fixed my dax according to your response and now I'm getting all the values, but I'm not getting ALL my values only those that meet one of the new columns. I can't add a filter to my report because I'm trying to use the dataset in paginated reports.

       

      Debbie

      • sevenhills's avatar
        sevenhills
        Super User
        4 years ago

        To assist further, can you share details / screenshots, where it is not working. 

         

         

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi Anonymous ,

    According to your description, here's my solution.

    CAPITAL_2022 = 
MAXX (
    FILTER (
        ALL ( 'GP_ALLOCATION_AND_FMSUMMARY_V' ),
        'GP_ALLOCATION_AND_FMSUMMARY_V'[FUNDING TYPE] = "Capital"
            && 'GP_ALLOCATION_AND_FMSUMMARY_V'[YEAR ID] = YEAR ( TODAY () )
    ),
    'GP_ALLOCATION_AND_FMSUMMARY_V'[TOTAL ALLOCATION]
)

    CAPITAL_2021 = 
MAXX (
    FILTER (
        ALL ( 'GP_ALLOCATION_AND_FMSUMMARY_V' ),
        'GP_ALLOCATION_AND_FMSUMMARY_V'[FUNDING TYPE] = "Capital"
            && 'GP_ALLOCATION_AND_FMSUMMARY_V'[YEAR ID] = YEAR ( TODAY () )-1
    ),
    'GP_ALLOCATION_AND_FMSUMMARY_V'[TOTAL ALLOCATION]
)

    CAPITAL_2023 = 
MAXX (
    FILTER (
        ALL ( 'GP_ALLOCATION_AND_FMSUMMARY_V' ),
        'GP_ALLOCATION_AND_FMSUMMARY_V'[FUNDING TYPE] = "Capital"
            && 'GP_ALLOCATION_AND_FMSUMMARY_V'[YEAR ID] = YEAR ( TODAY () )+1
    ),
    'GP_ALLOCATION_AND_FMSUMMARY_V'[TOTAL ALLOCATION]
)
    CAPITAL_2024 = 
MAXX (
    FILTER (
        ALL ( 'GP_ALLOCATION_AND_FMSUMMARY_V' ),
        'GP_ALLOCATION_AND_FMSUMMARY_V'[FUNDING TYPE] = "Capital"
            && 'GP_ALLOCATION_AND_FMSUMMARY_V'[YEAR ID] = YEAR ( TODAY () )+2
    ),
    'GP_ALLOCATION_AND_FMSUMMARY_V'[TOTAL ALLOCATION]
)

     Get the expected result.

    I have a doubt, why not directly write [YEAR ID]="2021", [YEAR ID]="2022" etc, as when it comes to 2023, the result of the above formula is wrong, the CAPITAL_2023 should be [YEAR ID]=YEAR(TODAY()).

     

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

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