MTD measure or column based on Date Slicer

[sclement]

Hi All,

I have  a table like below that is fetching from sql. 

'

I have a slicer in my report which takes the Calendar table date. I am after obtaing MTD for both products for the Date which are doesn't have any sales data for that date.

for eg: In the above table, I don't have sales data for 22/05/22, and when I select the date '22/05/22' in my slicer I would like to get  the below table.

 

Also, If I today is 30/05/2022, and there is no sales data since 23/05/2022, I want to  get the below table: 

I was wondering if there is any methods to accomplish the above requirement. It would be great if anyone could help on this.

Thanks in advance, S

[amitchandak · Accepted solution]

@sclement , have you already tried dates mtd with date table. Where date table is used in selection

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

Or formula based on today

https://medium.com/chandakamit/cheat-sheet-power-bi-time-intelligence-formulas-using-today-654f26e27...
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

[sclement]

Hi @amitchandak 

I think this solution worked well. Thank you very much for helping me on this.

Cheers!