cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

@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)) )

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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
) ) )

View solution in original post

2 REPLIES 2
malopez
Frequent Visitor

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
Not applicable

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
) ) )

View solution in original post

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!