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.