Dynamic Headers in DAX

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.

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!