Can not get MTD Prior Year, only total PY Month. Relative Filter, calendar w future dates

[Anonymous]

@PaulDBrown  Sorry but I'm caught here. 

I know I've done a filter with AND but I can't remember. 

I believe I need to filter with AND BI_Calendar[DateSales] = TRUE to limit the dates to yesterday (last sales date). 

PROBLEM

I can not get prior year MTD. All the posts addressing this issue show SAMEPERIODLASTYEAR as the Dax function to use. None work for me.  It delivers Total Prior year month.

Please help, thank you. I give KUDOS and mark as solved

It’s a daily report so RELATIVE DATE filter is wanted (In Current Year)

ABOUT THE DATA

Direct Query (very large company)

BI Calendar came from RADACAD and I added custom columns

BI_Calendar has future dates

BI_Calendar has a column that marks if the Date has sales, BI_Calendar[DateSales] (TRUE/FALSE) this seems the key but I can't figure out the filter in the DAX 

Fact_Sales[CalendarKey] ends the prior day, non-contiguous

NONE OF THESE WORK

This delivers nothing because the FILTER is in the wrong place I assume

Sales_USD_MTDPY =

CALCULATE( TOTALMTD ( [Sales_USD_Net_Dis] , BI_Calendar[Date] ) ,

SAMEPERIODLASTYEAR( BI_Calendar[Date] ) ,

FILTER( BI_Calendar , BI_Calendar[Date_Sales] = TRUE()) )

Sales_USD_MTDPY =

CALCULATE( TOTALMTD( [Sales_USD_Net_Dis]  , BI_Calendar[Date] ) ,

SAMEPERIODLASTYEAR( BI_Calendar[Date] ) )

Sales_USD_MTDPY =

CALCULATE( [Sales_USD_MTD]  , BI_Calendar[Date] ,

SAMEPERIODLASTYEAR( BI_Calendar[Date] ) )

Sales_USD_MTDPY =

CALCULATE( [Sales_USD_MTD]  , FACT_Sales[CalendarKey]  ,

SAMEPERIODLASTYEAR( BI_Calendar[Date] ) )

Sales_USD_MTDPY =

CALCULATE( TOTALMTD( [Sales_USD_MTD]  , BI_Calendar[Date] ) ,

SAMEPERIODLASTYEAR( BI_Calendar[Date] ) )

Last year Same month = CALCULATE([Sales_USD_NET_Dis] ,

DATESMTD(DATEADD(BI_Calendar[Date] , -1,YEAR)) )

[malopez]

I've been using the following pattern for similar situations. I'm not the original author, and unfortunately I don't remember where I got it from to properly credit her/him. Anyways, here it is:

RunningTotal2LY =
VAR curYrMinDate = MIN( 'Time'[PK_Date])
VAR curYrMaxDate = MAX( 'Time'[PK_Date] )
VAR minDate = DATE( (YEAR( curYrMinDate ) -2 ),MONTH(curYrMinDate), DAY(curYrMinDate))
VAR maxDate = DATE( (YEAR( curYrMaxDate ) - 2 ), MONTH(curYrMaxDate), DAY(curYrMaxDate))
RETURN
CALCULATE(
[Total Sales],
FILTER(
ALLSELECTED('Time'[PK_Date]),
'Time'[PK_Date] >= minDate && 'Time'[PK_Date] <= maxDate
)
)

One can substitute the value of 'curYrMaxDate' with the current date or let any filter context pass the values in for both 'curYrMaxDate' and 'curYrMinDate'.  Hope this helps.

[Anonymous · Accepted solution]

Found a solution from a site that I found and like: 

https://www.daxpatterns.com/standard-time-related-calculations/

The solution was a new calendar entry that needs to be a Measure, not a column. I had a column that did the same thing but it didn't work.

The Calendar Measure:

DatesWithSales =

VAR LastDateWithData =

CALCULATE (

FACT_Sales[Date_MAX] ,

REMOVEFILTERS ()

)

VAR FirstDateVisible =

MIN ( BI_Calendar[Date] )

VAR Result =

FirstDateVisible <= LastDateWithData

RETURN

Result

Then the Sales Measure can be made:

Sales_USD_PYMTD = IF (

[DatesWithSales] ,

CALCULATE (

[Sales_USD_MTD],

CALCULATETABLE (

DATEADD ( BI_Calendar[Date], -1, YEAR ),

BI_Calendar[Date_Sales] = TRUE

) ) )