Ytd up to this month

[admin_xlsior]

Hi,

How to have YTD up to this month, as well as YTD last year ?

My measure now is like this :

Purchase amount YTD CY = CALCULATE(
                                [Purchase amount],
                                DATESYTD('Purchase order dates'[Date])
                                )

Purchase amount YTD PY = CALCULATE(
                                [Purchase amount YTD CY],
                                SAMEPERIODLASTYEAR('Purchase order dates'[Date])
)

At this moment, my report is like this :

Thanks

[v-yuaj-msft · Accepted solution]

Hi @admin_xlsior ,

Based on your description, you can create two measures as follows.

Here is my test table.

Purchase amount = SUM('Table'[Value])

Purchase amount YTD CY =
IF (
MAX ( 'Table'[Date].[MonthNo] ) > MONTH ( TODAY () ),
BLANK (),
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Date] <= MAX ( 'Table'[Date] )
&& YEAR ( TODAY () ) = YEAR ( 'Table'[Date] )
),
[Purchase amount]
)
)

Purchase amount YTD PY =
IF (
MAX ( 'Table'[Date].[MonthNo] ) > MONTH ( TODAY () ),
BLANK (),
SUMX (
FILTER (
ALL ( 'Table' ),
YEAR ( 'Table'[Date] )
= YEAR ( MAX ( 'Table'[Date] ) ) - 1
&& MONTH ( 'Table'[Date] ) <= MONTH ( MAX ( 'Table'[Date] ) )
),
[Purchase amount]
)
)

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@admin_xlsior , Try like these examples with date tbale

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

YTD forced=
var _max = maxx('order',[Order date])
return
if(max('Date'[Date])<=_max, calculate([Purchase amount],DATESYTD('Date'[Date])), blank())
//calculate([Purchase amount],DATESYTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
//calculate(TOTALYTD([Purchase amount],'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD forced=
var _max1 =maxx('order',[Order date])
var _max = date(year(_max1)-1,month(_max1),day(_max1))
return
CALCULATE([Purchase amount],DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)