Fetch the sales of previous year same month until the last day of available data of this month

[Dinizov]

Hello,

I've got this source for my data which is a semantic model of a PBI where all the data is loaded.

For getting any periods sales I just have to use the measure for Total Sales called DailyValue Actual

I did a measure for getting me the sales of previous year by month/day/week as following:

PreviousYearSales =

    CALCULATE(

        [ValueDaily Actual],

        SAMEPERIODLASTYEAR('Tabelas Mês'[Date]) -- Use SAMEPERIODLASTYEAR for previous year sales

 )

and it works perfectly:

However I realised for the current month, if i have data only up to the 2nd of December, let's say, then it isnt comparing the same period for that month.

I have been trying to use anyway to calculate only the sales of previous year December for only 2 days but i simply cant do it.

I get a lot of errors, etc.

 The latest i've tried was to get it data from MTD only for this year and then use the same logics with SAMEPERIODLASTYEAR, but instead of getting only the data for December I am getting all the data from all the months:

MTDSales =

Var MyDay = DAY([LastDateCurrentYear])

VAR MyDate = DATE(YEAR([LastDateCurrentYear]),MONTH([LastDateCurrentYear]),1)

VAR DateRange =   DATESBETWEEN('Date'[Date], EOMONTH(MyDate, -1) + 1, MyDay )

RETURN CALCULATE([ValueDaily Actual], DateRange)

what is possibly wrong here?

I want to sum only the days of december of last year until a certain day and place it in the same chart with 2024 sales.

[v-stephen-msft]

Hi @Dinizov ,

Pls has your issue been solved? If so, accept the reply as a solution. This will make it easier for the future people to find the answer quickly.

If not, please provide a more detailed description, preferably some virtual sample data, and the expected results.

Best Regards,

Stephen Tao

[rajendraongole1 · Accepted solution]

Hi @Dinizov -create a measure that explicitly restricts the comparison to the relevant days of December. 

PreviousYearSales_SameDays =
VAR CurrentYearLastDate = MAX('Tabelas Mês'[Date]) -- Latest date in current year
VAR DayOfMonth = DAY(CurrentYearLastDate) -- Day of the month
VAR StartOfCurrentYearDecember = DATE(YEAR(CurrentYearLastDate), 12, 1) -- Start of December in the current year
VAR StartOfPreviousYearDecember = DATE(YEAR(CurrentYearLastDate) - 1, 12, 1) -- Start of December last year
VAR EndOfPreviousYearPeriod = DATE(YEAR(CurrentYearLastDate) - 1, 12, DayOfMonth) -- Equivalent last year's date
RETURN
CALCULATE(
[ValueDaily Actual],
DATESBETWEEN(
'Tabelas Mês'[Date],
StartOfPreviousYearDecember,
EndOfPreviousYearPeriod
)
)

create a similar measure for the current year's December sales up to the selected date

MTDSales_CurrentYear =
CALCULATE(
[ValueDaily Actual],
DATESMTD('Tabelas Mês'[Date])
)

Use the above measure, and i hope it works in your scenerio.