Forum Discussion

Tob_P's avatar
Tob_P
Icon for Helper V rankHelper V
4 years ago
Solved

SAMEPERIODLASTYEAR & Cumulative Total

Hi,

 

Couple of things I want to ask relating to SAMEPERIODLASTYEAR

 

My measure...

 

IVLA Actual SPLY = CALCULATE([Invoice Line Amount ACTUAL], SAMEPERIODLASTYEAR(Dates[Date]))
 
This works fine in a table when using Month as column, showing data for months so far this FY...
 
 
..but struggling to get my head around why it would show data for months in the future...
 
Not the end of the world, but would be cleaner if they didn't show?
 
Additionally, I would like to be able to show cumulative total for the total sales last year up to today's date last year, so in the example of the first table, the total sales between May & August last year. Is there a way to do this?
 
Finally, if I use a table to show the SAMEPERIODLASTYEAR measure, it gives me the total sales for the last year, rather than sales up to today's date last year in August.
 
 
Date table is fine, date column has individual dates in it...just not sure where I'm going wrong for something so straightforward?
  • johnt75's avatar
    johnt75
    4 years ago

    Its showing months in the future because, although you don't have any sales yet this year, you did have sales for those dates last year. Easiest way to fix that is to add a column to your date table, something like

    Today or before = 'Date'[Date] <= TODAY()

    and then filter your visual to show only when that is true.

    The problem with your table visual showing the values for the full year is happening because there is no filter on the date table - it will take the last date in your date table to work from, which is presumably 31st December 2022. You can either use the column generated above as a filter, again set to true, or use a relative date filter to show values in the last 0 or 1 calendar days, including today. That will give the values from Jan 1 to today last year. I think this will show what you are after for your second questions as well.

2 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    Its showing months in the future because, although you don't have any sales yet this year, you did have sales for those dates last year. Easiest way to fix that is to add a column to your date table, something like

    Today or before = 'Date'[Date] <= TODAY()

    and then filter your visual to show only when that is true.

    The problem with your table visual showing the values for the full year is happening because there is no filter on the date table - it will take the last date in your date table to work from, which is presumably 31st December 2022. You can either use the column generated above as a filter, again set to true, or use a relative date filter to show values in the last 0 or 1 calendar days, including today. That will give the values from Jan 1 to today last year. I think this will show what you are after for your second questions as well.

  • Tob_P's avatar
    Tob_P
    Icon for Helper V rankHelper V
    4 years ago

    johnt75- thank you for this, worked perfectly on both fronts. You've helped me a few times now so thank you again!