cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fsolarino
Frequent Visitor

SAMEPERIODLASTYEAR with YearToDdate filter based on This Year

Since I've had some trouble finding a solution to my problem in the community, I write this solution that might help somebody.

In this thread, I show my solution on how to obtain a Comparable period based on DATES of this year. Usually, in the real life, you don't have a full period this year to make a trend/comparison in a chart with previous year periods:

fsolarino_0-1623840606183.png

 

in the chart above, 2021 sales refer only to  01.Jan - 31.May period while the 2020 column refers to whole 2020.

What I wanted to show is the only 01.Jan - 31.May of 2020 period in order to have the "sameperiodlastyear" calculation for each year in the chart. 

The problem of using "SAMEPERIODLASTYEAR" function is that it automatically consider the actual context and switch to the previous similar period of the specific context. this means that in the chart, under 2020 column you would see the whole previous 2019 period instead of Jan-May.

 

Here is my solution:

basically we will set "FlagYTD" column in our datetable that we will use in our measuers or as a chart filter. This will be based on "month-day" so that for each year we will consider only days before the last day of this year.

1) first of all you need to create the "month-day" column in your DATETABLE (you can use also your fact-table):

 

 

 

month_day = 
    var monthN=DATETABLE[Data].[MonthNo]
    var dayN=DATETABLE[Data].[Day]
    return
        if(len( monthN)=1, "0","")&monthN
        &"-"&
        IF(len(dayN)=1, "0","")&dayN

 

this is what you should get

fsolarino_0-1623840763751.png

 

- adding leading zeros at the beginning allows the correct alphabetical order of month-days columns that can be used in a string comparison (<, >,)

2) at this point we will identify the last "month-day" of this year in the SALES / FACT table:

 

 

 

LAST_MONTH_DAY= 
    var lastday = calculate(max(SALESTABLE[Date]), all(SALESTABLE))
    var monthN=month(lastday)
    var dayN= day(lastday)
    return
        if(len( monthN)=1, "0","")&monthN
        &"-"&
        IF(len(dayN)=1, "0","")&dayN

 

and this is the two created columns together (in my case the last date in my dataset for this year is 29 may 2021): 

fsolarino_1-1623840815834.png

 

 

3) we can now create the "FLAG_YTD" column:

 

 

 

YTD Flag = DATETABLE[month_day]<=[LAST_MONTH_DAY]

 

 

 

 

Now you can either use the FLAG for calculated columns such as:

 

 

 

SALES_YTD= CALCULATE([InvNettoTot],CalDay_INV[YTD Flag])

 

 

 

 and use it as a double value column_chart or for any Comparison formulas (this can be used to calculate %detlas between periods )

fsolarino_1-1623840319187.png

Or maybe you can just filter out your chart (using the sum(SALES) measure) and obtain:

fsolarino_2-1623840418164.png

 

For me this was a very helpful trick that helped me out with lots of very useful charts and comparison.

 

i hope this will help someone of you too.

-francesco

2 REPLIES 2
v-easonf-msft
Community Support
Community Support

Hi, @fsolarino 

Thanks for your sharing.

Please add you reply and accept it as solution to close it .

So that other community members will easily find the solution when they get the same issue.

 

Best Regards,
Community Support Team _ Eason

amitchandak
Super User
Super User

@fsolarino , Based on what I got try a measure like this example

 

LYTD Corrected =
var _max = format(today(),"MMDD")
return
calculate(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),format('Date'[Date],"MMDD")<=_max)

 

 

You can use maxx selected date inplace of today 



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors