How to write dynamic years

[Anonymous]

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 TYPE	YEAR ID	TOTAL ALLOCATION
Capital	2024	0.00
Capital	2023	0.00
Capital	2021	6,849.00
Capital	2022	56,335.00
Desired Outcome
2021 Capital	2022 Capital	2023 Capital	2024 Capital
6,849.00	56,335.00	0.00	0.00

thanks,

Debbie

[v-yanjiang-msft · Accepted solution]

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]

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]

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]

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

[Anonymous]

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]

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