Reply
Regular Visitor
Posts: 27
Registered: ‎08-13-2018
Accepted Solution

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

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. 

 

last_processed.JPGCapture.JPG

 

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


Accepted Solutions
Regular Visitor
Posts: 27
Registered: ‎08-13-2018

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

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

View solution in original post


All Replies
Community Support Team
Posts: 7,674
Registered: ‎05-02-2017

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

Hi @ronakvi14,

 

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

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Regular Visitor
Posts: 27
Registered: ‎08-13-2018

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

I cant share the sample since data is confidential.

how can i resolve it otherwise

Community Support Team
Posts: 7,674
Registered: ‎05-02-2017

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

Hi @ronakvi14,

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor
Posts: 27
Registered: ‎08-13-2018

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

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