Measure with last know date form Sales table instead of YTD

[Farchurch]

Hi,

I use these formulas to get the YTD Gross profit well having my fiscal year starting in october and to compare it with the previous year.

• GrossProfit_YTD = IFERROR(TOTALYTD(Metingen[BWm Gross profit],Datumtabel[Date],"09/30"),BLANK() )
• GrossProfit_YTD-1 = TOTALYTD(CALCULATE([GrossProfit_YTD]), DATEADD(Datumtabel[Date],-365,DAY))
• GrossProfit_Growth = IFERROR( ((Metingen[GrossProfit_YTD]-Metingen[GrossProfit_YTD-1])/Metingen[GrossProfit_YTD-1]),

  BLANK() )

This works but i have another "problem". The Sales table data is not loaded in automatcly and not updated everyday.

So in the dashboard the date form YTD-1 moves forward and the comparcing doenst show the right data.

Is i possible to use the last know date form the sales table in stead of year to date? 

And for the prevooius year it would be the last know date in the sales table minus a year. 

thanks for a respone.

John

[amitchandak]

@Farchurch , Try these

YTD=
var _max = maxx(allselected('Table'),'Table'[order date])
var _min = date(year(_max),1,1)
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _max) )

LYTD=
var _max1 = maxx(allselected('Table'),'Table'[order date])
var _max = date(year(_max)-1,month(_max),day(_max))
var _min = date(year(_max),1,1)
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _max) )

All About Time Intelligence around Today: https://youtu.be/gcLhhxhXKEI

[Farchurch]

Hi @amitchandak 

When it looks at calander year it works perfect. the thing is my fiscal year start at 1 october.

Anyway to put that in the formula as well? 

kind regards, John

[Whitewater100]

Hi: 

If your measures are working then to handle your issue

Use in front of your LYLYD measure:

IF(ISBLANK(SUM(('Table'[Qty]), BLANK, "then put yourmeasure for LYYTD here")

[amitchandak · Accepted solution]

@Farchurch , Try like

YTD=
var _max = maxx(allselected('Table'),'Table'[order date])
var _min = if(month(_max) <10, date(year(_max)-1,10,1),date(year(_max),10,1) )
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _max) )

LYTD=
var _max1 = maxx(allselected('Table'),'Table'[order date])
var _max = date(year(_max)-1,month(_max),day(_max))
var _min = if(month(_max) <10, date(year(_max)-1,10,1),date(year(_max),10,1) )
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _max) )

[Farchurch]

@amitchandak the YTD works perfect. The LYTD i made a change in the second line 

var _max = date(year(_max1)-1,month(_max1),day(_max1))

but then somehow it still doenst give the right answer.

any idea?

regards John

[Farchurch]

@amitchandak 

my bad. it works fine if the second line of the LYTD is made as var _max = date(year(_max1)-1,month(_max1),day(_max1)).

Thanks a lot, i have seen that you are very active at this forum. Much appreciated