Previous Year MTD shows me wrong sum

[krisstok]

Hi, 

I got problem with proper sum of Previous Year up to specific day in current month using time intelligence. 

It looks like this:

Actual month is up to 12 day, but previous year shows me sum of whole month, not to this day, it should be 92.

Current dax formula:

Customers Qty MTD PY =
CALCULATE([Customers Qty],
DATESMTD(DATEADD('Calendar'[Date], -1, YEAR))
)

If I filter day 1 to 11 is ok (to 11 day)

I want to have always day slicer in default from 1 to 31. It works fine in other months.

I have a date table marked as date table with date up to max sales date (so 08-12-2022) and its connected to sales fact table by date 

I have tried many ways to solve this and none have the desired result, what I'm doing wrong?

[amitchandak · Accepted solution]

@krisstok , seem like GT getting last year full month

try like

LMTD =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = date(Year(_max1), month(_max1)-1, day(_max))
var _min = eomonth(_max1,-2)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

Comments after return are additional options

LYMTD QTY forced=
var _max = date(year(today())-1,month(today()),day(today()))
return
if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())
//OR
//CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALMTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...

[krisstok]

Wow man! It works! 

I adapted first formula and now sum is correct. Many thanks! 😄

Customers Qty MTD PY =
VAR _FilteredDate =
IF ( ISFILTERED ( 'Calendar' ), MAX ( 'Calendar'[Date] ), TODAY () )
VAR _max =
DATE ( YEAR ( _FilteredDate ) -1 , MONTH ( _FilteredDate ) , DAY ( _FilteredDate ) )
VAR _min =
EOMONTH ( _max, -1 ) + 1
RETURN
CALCULATE ( [Customers Qty], DATESBETWEEN ( 'Calendar'[Date], _min, _max )
)