Forum Discussion

auxilio99357's avatar
auxilio99357
Frequent Visitor
5 years ago
Solved

SAMEPERIODLASTYEAR()

Hello!!!

 

I´m building a sales report and have a problem with time intelligence mesures. 

on the report i have two data tables, the first one is a sales table with the following columns: client/date/units (for 4 years) and a date dimension table. The only relationship between them is a many to one single relationship of the date.

 

I want to compare this years sales with those of the previous year by using the following measures:

 

YTD = TOTALYTD(sum(Sales[U_Liq]),DimDates[Date])
LYTD = CALCULATE(sum(Sales[U_Liq]),SAMEPERIODLASTYEAR(DimDates[Date]))

 

The problem is that the LYTD measure adds up the sales for the whole year and I want it to add up only the months that elapsed in the current year (for ex. if YTD sums only the sales of january 2021, i want LYTD to sum only the sales od january 2020)

 

I´m showing both values in a Table.

 

Hope you can help me

Thank you!!

 

Need Help
  • BA_Pete's avatar
    BA_Pete
    5 years ago

    Hi auxilio99357 ,

     

    The previous code I supplied for DAX and Power Query M were both intended to be new columns in your calendar table, not measures.

     

    For your specific scenario i.e. sales only go up to two months ago, I would recommend adding a relative month column into your calendar table, something like this:

     

    DAX

     

    _relativeMonth = 
(YEAR(DimDates[Date]) * 12 + MONTH(DimDates[Date])) - (YEAR(TODAY()) * 12 + MONTH(TODAY()))

     

     

    PQ M

     

    (Date.Year([Date]) * 12 + Date.Month([Date])) - (Date.Year(DateTime.LocalNow()) * 12 + Date.Month(DateTime.LocalNow()))

     

     

    The usage of this in a measure would look something like this:

     

    LYTD = 
CALCULATE(
  SUM(Sales[U_Liq]),
  SAMEPERIODLASTYEAR(DimDates[Date]),
  DimDates[relativeMonth] <= -2
)

     

     

    Pete

10 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    5 years ago

    Hi auxilio99357 ,

     

    If you don't ever want to see last year's sales ahead of where you are in the current year, then the easiest way to do this is by adding a [currentDay] field to your date dimension. 

     

    In DAX, it would be something like this:

    currentDay =
IF(
  calendar[date] < TODAY(),
  "History",
  "Future"
)

     

    In Power Query M, it would be something like this:

    currentDay =
if [date] < Date.From(DateTime.LocalNow()) then "History" else "Future"

     

    You can then really easily filter pages, visuals, or even measures using this field to prevent 'future' dates from figuring into your report.

     

    Pete

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    5 years ago

    how are you displaying your data?  have you got it at the correct grain, can you share a screenshot?

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    Here is one way to do it without time intelligence.

     

    LYTD =
    VAR vMaxDate =
        MAX ( DimDate[Date] )
    RETURN
        CALCULATE (
            SUM ( Sales[U_Liq] ),
            FILTER (
                ALL ( DimDate[Date] ),
                DimDate[Date]
                    <= EOMONTH (
                        vMaxDate,
                        -12
                    )
                    && DimDate[Date]
                        >= DATE ( YEAR ( vMaxDate ) - 1, 1, 1 )
            )
        )

    Pat

     

  • Icey's avatar
    Icey
    Community Support
    5 years ago

    Hi auxilio99357 ,

     

    Just try this:

    LYTD =
CALCULATE ( [YTD], SAMEPERIODLASTYEAR ( DimDates[Date] ) )

     

     

     

    Best regards

    Icey

     

    If this post helps, then consider Accepting it as the solution to help other members find it faster.

    • auxilio99357's avatar
      auxilio99357
      Frequent Visitor
      5 years ago

      Hii Icey! 

       

      I tried using the measure:

      LYTD =
CALCULATE ( [YTD], SAMEPERIODLASTYEAR ( DimDates[Date] ) )

       but it didn´t work! it sums up the sales of the whole previous year instead of only the months of the current year. 

       

      Can you imagine why is this happening?

       

      Thank you!

      • BA_Pete's avatar
        BA_Pete
        Super User
        5 years ago

        Hi auxilio99357 ,

         

        I'll refer you back to my original answer i.e. create a 'currentDay' field in your calendar table with which you can filter pages, visuals or measures using [currentDay] = "History".

         

        It will solve this issue for you and, if you write the [currentDay] field into your usual calendar code, it will quickly solve any similar issues for you in the future.

         

        Pete