Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.