YTD function

[rajendraongole1]

Hi

 I want to calculate YTD for example from 01/01/2020 till 15/02/2020

I have a measure  "Sales LY YTD = TotalYTD(sum (Main_database[Sales]),SAMEPERIODLASTYEAR('Dates'[Date]))"

I split YTD by days and day by day results are correct but total (final ) result is strange

TotalYTD is calculating for whole month of Jan and Feb. how we can we get the data if i want to calculate for upto 15th of Feb'2020?

Please suggest.

[v-luwang-msft]

Hi, @rajendraongole1 

you could use the dax like below:

sy =
VAR mindate =
    DATE ( 2020, 01, 01 )
VAR maxday =
    DATE ( 2020, 2, 15 )
VAR last =
    CALCULATE (
        SUM ( Main_database[Sales] ),
        FILTER (
            ALL ( Main_database ),
            Main_database[date] <= MAX ( Main_database[Date] )
                && Main_database[Date] <= maxday
                && Main_database[Date] >= mindate
        )
    )
RETURN
    last

and you will get like below：

Wish  it is helpful for you!

Best Regard

Lucien Wang

[amitchandak]

@rajendraongole1 , when month is context for last month and last year, it will pass month end date

try like

YTD QTY forced=
var _max = today()
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESYTD('Date'[Date])), blank())
//or
//calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced=
var _max = date(year(today())-1,month(today()),day(today()))
return
if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())
//OR
//CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

or

YTD QTY forced=
var _max = maxx('order',[Order date])
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)

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