topic Re: Sales LY till same period in DAX Commands and Tips

Sales LY till same period

Jmrc16 — Wed, 03 Sep 2025 15:37:55 GMT

Hello

I have a report with the measure:

CALCULATE(

[Vendas],

SAMEPERIODLASTYEAR('Date Table'[Date]), 'Date Table'[Month Number]<=MONTH(MAX(SaleTransactionDetails[CreateDate]))

It gives me the LY sales till the same month of the maximum sales dates. However, how can I do it if I want to calculate the LY sales till the same last year date?
For example, if I have sales between 01/01/2025 and 03/09/2025, sales LY sould sum the lst year sales between 01/01/2024 and 03/09/2024.

Re: Sales LY till same period

johnt75 — Wed, 03 Sep 2025 15:45:15 GMT

You could use

Sales LY = VAR MaxDate = CALCULATE ( MAX ( SaleTransactionDetails[CreateDate] ), REMOVEFILTERS ( 'Date Table' ) ) VAR DatesThisYear = DATESBETWEEN ( 'Date Table'[Date], DATE ( YEAR ( MaxDate ), 1, 1 ), MaxDate ) VAR Result = CALCULATE ( [Vendas], SAMEPERIODLASTYEAR ( DatesThisYear ) ) RETURN Result

Re: Sales LY till same period

FBergamaschi — Wed, 03 Sep 2025 17:51:26 GMT

I would suggest to

1 - create a calculated column in the Calendar table

Past present Future =

VAR _MaxSalesDate = MAX ( Sales[Date] )

RETURN

IF (

'Calendar'[Date] < _MaxSalesDate,

"Past",

IF (

'Calendar'[Date] = _MaxSalesDate,

"Present",

"Future"

)

2 - use this DAX template code for the measure

Sales LY up to Max Date Current Year =

CALCULATE(

[Sales],

CALCULATETABLE(

SAMEPERIODLASTYEAR('Calendar'[Date]),

'Calendar'[Past present Future] <> "Future"

)

In this way you have a very fast DAX and a simple implementation

The result is here (Sales started i n2023 and in 2023 are up to dicember - full year - , but you see only up to july as in 2024 since last Sale date is in july 2004, this is your case)

If you want to see details, file is here for your convenience

https://drive.google.com/drive/folders/1syEbfXegdWm_ZT7kW4SsMXFvWjgLj0gz?usp=drive_link

If this helped, please consider giving kudos and mark as a solution

mein replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Sales LY till same period

Jihwan_Kim — Thu, 04 Sep 2025 02:56:42 GMT

Hi,

Please try something like below whether it works.

And, for your reference, please check the attached pbix file.

Last Year expected result: = VAR _maxdate = MAX(SalesTransactionDetails[CreateDate]) VAR _untilmaxdate = ADDCOLUMNS( FILTER( 'Date Table', 'Date Table'[Date] <= _maxdate ), "@lastyearsales", CALCULATE( [Vendas], SAMEPERIODLASTYEAR('Date Table'[Date]) ) ) RETURN SUMX( _untilmaxdate, [@lastyearsales] )

Re: Sales LY till same period

Anonymous — Thu, 04 Sep 2025 11:29:27 GMT

Hi Jmrc16 ,

Thank you Jihwan_Kim and FBergamaschi for the response provided!

Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Thank you for your understanding!

Re: Sales LY till same period

Jmrc16 — Thu, 04 Sep 2025 11:56:07 GMT

I tried this solution, but when I put the measure in a matrix and put month in the lines, value doesn´t filter, it only shows the same value for al months. I think it's the "removefiltrs" impact

Re: Sales LY till same period

Jmrc16 — Thu, 04 Sep 2025 15:25:42 GMT

Hi Anonymous ,

Yes, I have already marked the solution.

thank you all