Forum Discussion

Dinizov's avatar
Dinizov
New Member
1 year ago
Solved

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

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.
General Comment
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    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. 

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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