Missing Dates in end of Month, cause problem in Sales LY

[kostask]

Hello guys.

I've been struggling for several hours with this problem.

I have my dataset with sales per day, connected with a calendar table. 30 and 31/1 were nonworking days, so the dates are missing from my Sales table.

Everything works ok for TY, but for the previous year, I can't pick the results I want at the monthly level.
I tried many ways

Below the results and my Calculations:

LYTD V1 = CALCULATE( [Total sales], SAMEPERIODLASTYEAR('Calendar'[Date]))

LYTD V2 = CALCULATE( [Total sales], DATEADD('Calendar'[Date],-1,YEAR))

**bleep** LYTD = CALCULATE( [LYTD V1], FILTER( ALLSELECTED('Calendar'),'Calendar'[Date] <= MAX('All Sales'[Date])))

What finally I want to see for LYTD results (like YTD) is full sales of January and the cumulative sales for February only in the next cell:

Jan 1.906.767
Feb          316

It seems that I have to do with the missing dates, and also with the ability to not show the totals after the max day of Sales.

Any help or guidance appreciated 🙂

Kostas

[amitchandak]

@kostask , for LYTD have tried like

Last YTD Sales = CALCULATE( [Total sales],DATESYTD(dateadd('Calendar'[Date],-1,Year),"12/31"))

[kostask]

@amitchandak , unfortunately, it gives the cumulative sales of the entire PY TD  and not until the max sales date of the current February.

My problem.. 😑

Thanx

[amitchandak]

@kostask , That we can limit. you can eomonth on _max to get full Feb last year

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)

[kostask]

This is so hard 😵

It works and indeed it stops at the end of the month (and not the entire year).

The problem is that firstly it gives results for the full month (we want to stop at the max sales day ), and also cumulatively since day 1 of the year and not the day 1 of the month.

I think the problem is that we work in month context on the table, which must be removed.

Thanx