Sales LY till same period

[Jmrc16]

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.

[FBergamaschi · Accepted solution]

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

[v-tejrama]

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!

[Jmrc16]

Hi @v-tejrama ,

Yes, I have already marked the solution.

thank you all

[Jihwan_Kim · Accepted solution]

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]
		)

[FBergamaschi · Accepted solution]

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

[johnt75]

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

[Jmrc16]

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