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.

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.  

 

 

sevenhills's avatar
sevenhills
Super User
4 years ago

Tough to visualize. If it is working on one and not working on other, I suggest you do these 

 

a) Go to PBI desktop, click performance analyzer, start recording or refresh visuals and get the dax queries. Now, you compare against the Paginated report query you wrote.

 

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-performance-analyzer

 

If you can figure out the issue, then it is great. If not then do (b)

 

b) Create mockup data and attach the files to this thread