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/d...
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

Icey's avatar
Icey
Icon for Community Support rankCommunity 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
    Icon for Super User rankSuper 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

    • auxilio99357's avatar
      auxilio99357
      Frequent Visitor
      5 years ago

      Hi, @BA_Pete !!

       

      When trying to add the field I think I understood why all the time intelligent measures don´t work as I expect. My Sales table will always contain the value of sales up to two months before the current day. 

       

      So now I want to create a 'CurrentDay' with the condition that the current day is two months from the actual current day.

       

      To do that I'm trying to adapt the 'CurrentDay' field you specified. 

      First I wanted to create the field exactly as you wrote it by adding a new measure to the DimDates table and by adding a new table. In both times, when when I wrote the code, I couldn´t write "DimDates[Date]" after the IF funtion.

       

      Is this correct or did I misunderstand?

       

      Thank you 

      😊

       

      • BA_Pete's avatar
        BA_Pete
        Icon for Super User rankSuper User
        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