Forum Discussion

Jmrc16's avatar
Jmrc16
Frequent Visitor
1 year ago
Solved

Sales LY till same period

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.
@Dax
  • FBergamaschi's avatar
    FBergamaschi
    1 year ago

    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

  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

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

     

6 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      Jmrc16
      Frequent Visitor
      1 year ago

      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

  • FBergamaschi's avatar
    FBergamaschi
    Icon for Super User rankSuper User
    1 year ago

    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

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    1 year ago

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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      Jmrc16
      Frequent Visitor
      1 year ago

      Hi Anonymous ,

      Yes, I have already marked the solution.

      thank you all