Dynamic Headers in DAX

[MStark]

Hey all!

Im working on a query and wondering how to get header dates to be dynamic. Right now Im summarizing columns with the actual date but need this to be updated to be dynamic so can just set the filter to be the last 5 days. Any help with this will be appreciated!!

DEFINE

    VAR __DS0Core =

        SUMMARIZECOLUMNS(

            'Sheet1'[Location],

            'Sheet1'[Date],

            "SumAmount", SUM('Sheet1'[Amount])

        )

    VAR __Pivoted =

        ADDCOLUMNS(

            SUMMARIZE(__DS0Core, 'Sheet1'[Location]),

            "2025-09-11", CALCULATE(SUM('Sheet1'[Amount]), 'Sheet1'[Date] = DATE(2025, 9, 11)),

            "2025-09-12", CALCULATE(SUM('Sheet1'[Amount]), 'Sheet1'[Date] = DATE(2025, 9, 12)),

            "2025-09-13", CALCULATE(SUM('Sheet1'[Amount]), 'Sheet1'[Date] = DATE(2025, 9, 13)),

            "2025-09-14", CALCULATE(SUM('Sheet1'[Amount]), 'Sheet1'[Date] = DATE(2025, 9, 14)),

            "2025-09-15", CALCULATE(SUM('Sheet1'[Amount]), 'Sheet1'[Date] = DATE(2025, 9, 15))

        )

EVALUATE

    __Pivoted

ORDER BY

    'Sheet1'[Location]

Thanks in Advance!!

[tayloramy · Accepted solution]

Hi @MStark, 

What tools are you using? As far as I know DAX wasn't designed to be able to handle this and can't do it.  

If this helps, please consider giving Kudos. If it solves your problem, mark it as the solution so others can find it faster.

[ZhangKun · Accepted solution]

DAX queries cannot generate dynamic column headers. You can use alternative solutions:

1. Filter rows for the last five days in Power Query. See the attached example for a simple example. (For testing, you'll need to manually set your computer's time, but remember to turn "Set Automatic Time" back on.)

2. Use headers like "1 Day Ago" and "2 Days Ago."

[v-sgandrathi]

Hi @MStark,

Has your issue been resolved?
If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Thank you @tayloramy for your conversation in the threads and your continuous effort in resloving the queries.

Thank you for your understanding!

[v-sgandrathi]

Hi @MStark,

As we have not received a response from you yet, I would like to confirm whether you have successfully resolved the issue or if you require further assistance.

Thank you for your cooperation. Have a great day.

[v-sgandrathi]

Hi @MStark,

We haven’t heard from you on the last response and was just checking back to see if your query was answered. Otherwise, will respond back with the more details and we will try to help .

Please let us know if there’s anything else we can do to help.

Thakn you.

[tayloramy]

Hi @MStark, 

You can make the dates dynamic, but there are two important points:

1. In a DAX query you cannot generate truly dynamic column names (headers must be static strings).
2. The easiest dynamic experience is to return [Location], [Date], [SumAmount] for the last 5 days and let a Matrix visual put Date on Columns with a Relative date filter.

Below is my recommendation:

This keeps the query simple and lets the visual handle the headers.

DEFINE 
    MEASURE 'Sheet1'[Sum Amount] = SUM ( 'Sheet1'[Amount] ) 

EVALUATE 
VAR AnchorDate = 
    CALCULATE ( MAX ( 'Sheet1'[Date] ), ALL ( 'Sheet1' ) ) // last date in data 
VAR Last5 = 
{ AnchorDate - 4, AnchorDate - 3, AnchorDate - 2, AnchorDate - 1, AnchorDate } 
RETURN 
    SUMMARIZECOLUMNS ( 
        'Sheet1'[Location], 
        'Sheet1'[Date], 
        TREATAS ( Last5, 'Sheet1'[Date] ), 
        "SumAmount", [Sum Amount] ) 
ORDER BY 
    'Sheet1'[Location], 'Sheet1'[Date] 

Then in the report:

• Drop this table into a Matrix.
• Put Location on Rows, Date on Columns, and SumAmount as Values.
• Use a Relative date filter set to Last 5 days. Docs: Relative date slicer/filter.

Reference docs for functions used: SUMMARIZECOLUMNS, DAX Guide: SUMMARIZECOLUMNS.

If you have a proper Date table related to Sheet1[Date] and marked as a date table (Mark as date table), you can filter with:

VAR Last5 = DATESINPERIOD ( 'Date'[Date], AnchorDate, -5, DAY ) 

Function ref: DATESINPERIOD, DAX Guide: DATESINPERIOD.

If this helps, please consider giving Kudos. If it solves your problem, mark it as the solution so others can find it faster.

[MStark]

Hi @tayloramy,

Thanks for taking the time and responding! Im using this query to get data and create reports outside of BI which is why I need the dynamic dates as column headers. Any other solutions?

[tayloramy · Accepted solution]

Hi @MStark, 

What tools are you using? As far as I know DAX wasn't designed to be able to handle this and can't do it.  

If this helps, please consider giving Kudos. If it solves your problem, mark it as the solution so others can find it faster.