SamePeriodLastYear block on last day of current year.

[Anonymous]

Hello,

I'm trying to create a report with sales this year compare to the same period LY but I would like to block it at the same day.

As example below I would like that to compare 2020 and 2019 in August and to be comparable, 2019 need to be stop at the last day of 2020.

My formula is

Net Sales LY = 
    CALCULATE(sum('B&M_Database_All'[Net Sales]),
    SAMEPERIODLASTYEAR('Dim Date'[Date]))

How I can do that ?

Thanks in advance for your help.

[Anonymous · Accepted solution]

Net Sales LY =
var __latestDateAvailable =
    CALCULATE(
        MAX( 'B&M_Database_All'[Date] ),
        ALL( 'B&M_Database_All' )
    )
RETURN
CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR(
        FILTER(
            VALUES( 'Dim Date'[Date] ),
            'Dim Date'[Date] <= __latestDateAvailable
        )
    )
)

[Total Sales] should be the very simple measure that just returns the total sales. Something like SUM( FactTable[Sales Amount] ).

[Anonymous]

Thanks, this one is ok and @amitchandak if I add to your formula the code as below, it's ok.

var _max1 = calculate(maxx('B&M_Database_All',[Date - Billing]),All('B&M_Database_All'))

Thank you both for your help.

[amitchandak]

@Anonymous , hope you formula working fine.

Try like

Net Sales LY=
var _max = date(year(today())-1,month(today()),day(today()))
return
CALCULATE(sum('B&M_Database_All'[Net Sales]),
SAMEPERIODLASTYEAR('Dim Date'[Date]), 'Dim Date'[Date] <=_max)

make sure date and year in visual table come from Date dim

[Anonymous]

@amitchandakthanks your proposal function 🙂

Another question, for example in this case, I have not update my database since August 9th, but actual day is August 14th.

Is it possible to block it to the last data fill in 2020 ?

Thanks a lot for your help !

[amitchandak]

@Anonymous , Take a date from fact

Net Sales LY= 
var _max1 =maxx('B&M_Database_All',[date])
var _max = date(year(_max1)-1,month(_max1),day(_max1))
return
  CALCULATE(sum('B&M_Database_All'[Net Sales]),
    SAMEPERIODLASTYEAR('Dim Date'[Date]), 'Dim Date'[Date] <=_max)

[Anonymous]

@amitchandak 

Thanks, I have try it, but it generate an error :

Net Sales LY Test 2 = 
    var _max1 =maxx('B&M_Database_All',[Date - Billing])
    var _max = date(year(_max1)-1,month(_max1),day(_max1))
    return 
    CALCULATE(sum('B&M_Database_All'[Net Sales]),
    SAMEPERIODLASTYEAR('Dim Date'[Date]),'Dim Date'[Date]<=_max)

On total level, it's ok, but when I want to check day by day or filter on a specific month it generate an error.