days sale last year not coming out right ( fetch max date as last processed date)

[Anonymous]

Hi Team, 

I have been struggling since a week for this. 

Below is showing proper results: 

Total Sales CY Yesterday = CALCULATE([Total Sales CY], LASTDATE('Financial Data'[date_dt]))

(Total Sales CY = CALCULATE([Total Sales],'Financial Data'[Year] = YEAR(TODAY()) )

THIS IS NOT WORKING (below) 

If i try 

Method 1:

Total Sales LY Yesterday = CALCULATE([Total Sales],FILTER(('Financial Data'),'Financial Data'[date_dt]= [Last processed date LY]))

(Last processed date LY = DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(LASTDATE('Financial Data'[date_dt])))

Method 2: 

Total Sales LY Yesterday2 = CALCULATE([Total Sales LY], FILTER(('Financial Data'), 'Financial Data'[date_dt].[Date]=[sameperiod last year]))

(

Total Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Financial Data'[date_dt].[Date]))

sameperiod last year = DATE(YEAR(NOW())-1,MONTH(NOW()), DAY([Last processed date CY]))

)

both methodsdidnt work. 

Am using lastdate function because for monday (i cant use sundays data -which would be blank) i need to process lastdate which has sales

[v-jiascu-msft]

Hi @Anonymous,

Can you share a sample, please? The formula looks good. Didn't it return the expected result? 

Best Regards,
Dale

[Anonymous]

I cant share the sample since data is confidential.

how can i resolve it otherwise

[v-jiascu-msft]

Hi @Anonymous,

Can you make some dummy data? I would also suggest you try to add a date table that is necessary for the time intelligence functions.

Best Regards,
Dale

[Anonymous · Accepted solution]

Actually, I resolved it !!! Total Sales LY Yesterday = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(LASTDATE('Financial Data'[date_dt])))