YTD for previous month to date

[NatK]

I'm trying to come up with a calculation for the year to date sales of the previous month-to-date. 

For example, my current date is 11/18/2021. My previous month to date is 10/18/2021. I am looking for the YTD amount of the previous month-to-date.  I would like to return sales from 1/1/2021 through 10/18/2021.

Current date = 11/18/2021

Current MTD = 11/1/2021 - 11/18/2021

Previous Month MTD = 10/1/2021 - 10/18/2021

YTD of Previous Month MTD = 1/1/2021 - 10/18/2021 (This is what I'm looking for)

Any suggestions??

Thanks!!

[v-yiruan-msft · Accepted solution]

Hi @NatK ,

I created a sample pbix file(see attachment), please check whether that is what you want. You can create a measure as below to get the YTD for previous month to date:

YTD for previous month to date = 
VAR _seldate = SELECTEDVALUE ( 'Date'[Date] )
VAR _year =  YEAR ( _seldate )
VAR _month = MONTH ( _seldate )
VAR _day =   DAY ( _seldate )
VAR _presmonth =  IF ( _month = 1, DATE ( _year - 1, 1, 1 ), DATE ( _year, 1, 1 ) )
VAR _premaxday =  EOMONTH ( DATE ( _year, _month - 1, 1 ), 0 )
VAR _preemonth =
    IF (
        _month = 1,
        DATE ( _year - 1, 12, _day ),
        IF ( _day > DAY ( _premaxday ), _premaxday, DATE ( _year, _month - 1, _day ) )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Date] >= _presmonth
                && 'Table'[Date] <= _preemonth
        )
    )

If the above one is not what you want, please provide some sample data(exclude sensitive data) and your expected result with calculation logic and special examples. Thank you.

Best Regards

[amitchandak]

@NatK , With help from Time Intelligence and date table, Example

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

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

[NatK]

Thanks @amitchandak. I am familiar with the time intelligence functions. My problem, however, is less straightforward. I am looking for YTD of Previous Month MTD. My report also has a 'month year' slicer. I came up with a solution, although it is quite long. I'm sure there is a shorter version out there, but here is my solution in case someone else may have the same question (If anyone comes up with a shorter solution, please share!):

Purchase Order Count YTD 1 Month Prior =

VAR LASTDAY = LASTDATE('Date'[TheDate])

VAR LASTDAYnMOPRIOR = DATE(YEAR(LASTDAY), MONTH(LASTDAY)-1, DAY(LASTDAY))

VAR CURRMO_TOTAL = --CALC FOR CURRENT MONTH

CALCULATE([Purchase Order Count], FILTER(ALL('Date'),

AND(

'Date'[TheDate] <= LASTDAYnMOPRIOR,

'Date'[TheYear] = YEAR(LASTDAYnMOPRIOR)

)

))

VAR nMOPRIOR_TOTAL = --CALC FOR PREVIOUSMONTHS

CALCULATE (

[Purchase Order Count],

DATESYTD ( 'Date'[TheDate] ),

MONTH ( 'Date'[TheDate] ) < MONTH ( LASTDAY )

)

RETURN IF(MONTH (MAX('Date'[TheDate]) ) = MONTH(MAX('Last Dataset Refresh'[Last Dataset Refresh (PBI Service)])), CURRMO_TOTAL, nMOPRIOR_TOTAL)

[amitchandak]

@NatK , DO you need YTD till last date

YTD Sales =
var _1 = maxx(allselected('Date'),'Date'[Date])
var _max = date(year(_1) , month(_1) -1 , day(_1))
return
CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"), 'Date'[Date]<= _max)

[NatK]

Yes, I need YTD to last date. I tried the measure you provided. It works for the current mtd. If I select a prior month (i.e. October 2021), then the date range it returns is 1/1/2021- 10/1/2021. The range should be 1/1/2021-9/30/2021.