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

BA_Pete's avatar
BA_Pete
Super 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

auxilio99357's avatar
auxilio99357
Frequent Visitor
5 years ago

Thank you @BA_Pete !!

 

I manage to solve most of the issues by adding that column

 

Regards 🙂

Auxilio99357