DAX formula in tabular model showing (blank) when calculating MTD total purchases

1
votes

I am writing DAX in a ssas tabular model and running into a problem.

I wish to calculate the total amount purchased MTD.

I have a measure which calculates the total purchased which works fine however when I use the below queries they will not produce the expected result...

Total Purchased MTD:= CALCULATE([Total Purchased],DATESMTD('Date'[Date]))
Total Purchased YTD:= CALCULATE([Total Purchased],DATESYTD('Date'[Date]))

Total Purchased calculation:

Total Purchased:= CALCULATE([Total Orders],'Order'[Is Sale] = TRUE())

I have checked and there is an active relationship between the date and order table.

Any help would be appreciated.

Thanks

Will

1
sql-serverssasdax
Did you mark your 'Date' table as a Date Table? – mxix
Hi mxix, yes i have marked it as a date table and processed, no change i am afraid – Will
I would provide some sample data and expected outcome. – mxix

1 Answers

0
votes

You can change DATESMTD to this calculation (this is syntax sugar for):

DATESBETWEEN ( 
    <Dates>, 
    STARTOFMONTH ( LASTDATE ( <Dates> ) ),
    LASTDATE ( <Dates> ) 
)

check what every part of this calculation return for you.