I am trying to create a DAX measure in EXCEL because the client requires Excel to be used.
The Excel power query combines sales data that is extracted every day and has a run date column as well as the normal sales date column.
The power pivot report needs slicers for year, month name and day and the sales value required should be MTD(1-18th Feb) but only for data rows where the run date equals the slicer day e.g. 18-Feb
I tried the DAX in PBI where this measure works
Intakes MTD for run rate =
CALCULATE(CALCULATE(Intakes[Intake Value], DATESBETWEEN(DimDate[Date], STARTOFMONTH(DimDate[Date]), MAX(DimDate[Date]))),
USERELATIONSHIP(DimDate[Date], Intakes[Run Date] ), Intakes[Run Date] = MAX(DimDate[Date] ))
But I can’t get it to work in Excel
The attached workbook gives enough rows to replicate the scenario and the power pivot showing the DAX measures I have tried, thank you
Test DAX.xlsx (743.1 KB)