SAMEPERIODLASTYEAR, but not for current month

[PowerRon]

Hi,

I have a clustered colum chart, where previous year is compared to current year for every month.
Using SAMEPERIODLASTYEAR.


Works fine, except for the current month. 
Because when it is for instance 3rd of May, the grey bar (previous year) shows everything up until the 3rd of May.
But we are always one day behind with the batch. So on the 3rd of May for the current year (blue bar) we only have data up until 2nd of May. So for the current month it would fit better, to have both bars up until 2nd of May.

I am now using this DAX for previous year

CALCULATE ( SELECTEDMEASURE (), DATEADD ( 'Calendar Local Date'[Date Local], -1, YEAR ) )

How to change to get it working in the way I want?

Regards
Ron

@OwenAuger 
@MFelix 

[OwenAuger]

Hi @PowerRon 

You could follow the method in this article:

https://www.sqlbi.com/articles/hiding-future-dates-for-calculations-in-dax/

The idea is to identify the maximum date for which you have data in your fact table, and add a flag in the Date table that is true for dates on or before this date.

Also, by any chance are you truncating your 'Calendar Local Date' table mid-month? If so, I would recommend instead including complete months and complete years to avoid any issues with time intelligence functions.

Here are the steps you could follow:

1. Create this calculated column in 'Calendar Local Date':

DatesWithSales =
    'Calendar Local Date'[Date Local] <= MAX ( 'Fact Table'[Date] )

• Replace 'Fact Table'[Date] with the appropriate fact table column reference).
• If you need different logic for the "max" date, adjust the MAX ( ... ) expression.

2. Use this DAX expression for previous year calculations:

CALCULATE (
    SELECTEDMEASURE (),
    CALCULATETABLE (
        DATEADD ( 'Calendar Local Date'[Date Local], -1, YEAR ) ),
        'Calendar Local Date'[DatesWithSales] = TRUE
    )
)

Does something like this work for you?

[PowerRon]

Thnx @OwenAuger for your reaction.
I am not truncating my Calendar table mid-month.
In your solution I have to add a calculated column. OK.

But do you think there is also a solution where you just change the DAX?
Something like
If year-month in filter context = year-month in today()
subtract 1 day from sameperiodlastyear-formula
else just use sameperiodlastyear

[OwenAuger · Accepted solution]

You're welcome @PowerRon

And sorry for the incorrect assumption I made.

Yes, you can do the same thing without a calculated column.

The most general way to write it would be like this, using TODAY() - 1 as the threshold:

VAR DateThreshold =
    TODAY () - 1 -- Max allowable date
RETURN
    CALCULATE (
        SELECTEDMEASURE (),
        CALCULATETABLE (
            SAMEPERIODLASTYEAR ( 'Calendar Local Date'[Date Local] ),
            KEEPFILTERS ( 'Calendar Local Date'[Date Local] <= DateThreshold )
        )
    )

Does this work for you?

Regards

[PowerRon]

Hi @OwenAuger thnx for the answer.
In the end the user doesn't see it as a problem for now, so I won't change it. But I will keep this solution in mind. Thnx