Month to date by slicer

[KyawMyoTun]

Dear Experts,

Currently I am comparing two consecutive months in Power Bi.
The current month "Jan-2020" has only 6(1~6) days sales. So,I'd like to compare by the 6(1~6) days sales of "Dec-2019".
But I am getting the whole month sales of "Dec-2019".
If I select "Dec-2019" in slicer, it is working correctly becuase of the whole month sales data for both Nov and Dec.

DAX formulas:

Current Month Sales = CALCULATE(

[Total Sales],

DATEADD(FILTER(DATESMTD('Calendar'[Date]), 'Calendar'[Date] < TODAY()),0, MONTH))

Previous Month Sales = CALCULATE(

[Total Sales],

DATEADD(FILTER(DATESMTD('Calendar'[Date]), 'Calendar'[Date] < TODAY()), -1, MONTH))

Please help me to fix this.

[amitchandak]

Move dateadd inside and check

example

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

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[KyawMyoTun]

@amitchandak,

   I have moved dateadd inside but the total sales is still getting the whole month of "Dec-2019".
Any other formula to fix this?

[amitchandak · Accepted solution]

You are selecting month not date that is the problem
Create a var of day and use <= of that

last MTD Sales =
var _day = day(today)
return
CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)), day(date[date])<= _day)

[KyawMyoTun]

@amitchandak,

I was calculating by day(today()) function and found that
today is "22 Jan 2020" and the calculation was made based on today date.
If I select the "Dec - 2019" in slicer, It only calulate based on today date and sale is 1~22 data only.
Actually it should be calculate based on the whole month for Nov and Dec 2019.
So, I tried by the max date and it become the desire result.
Thank you so much @amitchandak.

Dax Formulas,
Current Month Sales = var _day = DAY(MAX('Calendar'[Date]))
return
CALCULATE([Total Sales],DATESMTD(dateadd('Calendar'[Date],0,MONTH)), day('Calendar'[Date])<= _day)