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

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. 

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        The top screen print is what I'm trying to create in PBI. Both reports are sorted ascending. In the PBI report I would expect to see all the projects listed in the top screen print. None of the request in the Current Report Output have any values in the columns I’ve created in PBI.