Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Anonymous
Not applicable

YTD Comparisons of Current, Last Year and 2 Years Prior with Irregular Calendar Years

I've been trying to get YTD measures to compare the current YTD against last year and 2 years ago YTDs.  I was able to get the current YTD, but have been having trouble with the other two.  2016 is made up of 14 months and goes from Nov 2015 through Dec 2016 and 2015 starts on Nov 2014 through Oct 2015.  

 

I have a table with account data that has dates and sales.  I have also created a calendar table with dates, months, years etc. that's linked to the sales table. I have the following measures:

 

Total Sales = SUM(Sales[Sales])

CY YTD = TOTALYYTD([Total Sales], Sales[Date]) 

 

When I try to get LY YTD, I can't seem to get the amounts from Nov and Dec 2015 to be included.  How can I get the time intelligence functions to adjust to these particular years?  Thanks for your time and help.  

 

1 ACCEPTED SOLUTION

Hi @Anonymous,

 

I downloaded the YTD_test pbix sent by you.

 

The approach I had taken is as under.

 

1.  I changed your Dates table formation as  -  Dates = CALENDAR(MIN(Sales[Date]), MAX(Sales[Date]))

     This is the best way to create a calendar table based on the minimum and maximum date of the fact tables.

 

2.  Created a column called MonthNumber = Month(Dates[Date])

3.  Set the MonthName to sorted by column MonthNumber.

4.  Created a MonthSequentialNumber column

     MonthSequentialNumber = ('Dates'[Year] - MIN( 'Dates'[Year] )) * 12 + 'Dates'[MonthNumber]

    This generates a sequential number for each month and incremented by 1 for every month in the Dates table.

    In the sample file this number ranges from 11 to 45.

    To go back 1 year just need to subtract 12 from this. Similarly to go back 2 years subtract by 24 and so on.

 

5.  Created a measure called

     YTD1YearB4 = Calculate([Total Sales],Filter(ALL(Dates), Dates[Year] = Max(Dates[Year]) - 1  && Dates 

                                          [MonthSequentialNumber] <= Max(Dates[MonthSequentialNumber]) - 12) )

 

6. Created a measure called

    YTD2YearB4 = Calculate([Total Sales],Filter(ALL(Dates), Dates[Year] = Max(Dates[Year]) - 2  && Dates

                                                    [MonthSequentialNumber] <= Max(Dates[MonthSequentialNumber]) - 24) )

 

Depending on the Year selected it will report the 1year before and 2 year before total sales.

 

I have  uploaded the file in one drive. and the link is

 

https://1drv.ms/u/s!ApP3mBZyGaHfzyeQc9QxgrROjsFD

 

If this works for you please accept this as a solution and also give KUDOS.

 

Cheers

 

CheenuSing

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

14 REPLIES 14
krider71
Frequent Visitor

Hi CheenuSing,

 

Thank you.  It worked except I keep running into the same issue I have when I am using the formula to get last year numbers using the sameperiodlastyear dax formula.  I think it ihas something to do with 53wks.  Not sure how to work around that issue in your formula.  This is how I have to work around the sameperiodlast year issue:    Year behind Sales = CALCULATE(SUM([Total Sales Amount]),dateadd('Dates'[Date],-364,Day))

 

In your the formula YTD 3/12/22:  last year is using 12/26/21-3/5/2022 vs 1/2/2022-3/12/2022.

 

krider71_1-1679193565627.png

 

Any help is appreciated!!

 

 

 

 

 

hemantsingh
Helper V
Helper V

Hi @Anonymous,

 

   did you get any solution to this??? If yes then kindly share.

 

Regards,

Hemant

MFelix
Super User
Super User

Hi @Anonymous,

 

Try to do something like this for previous year:

 

LY YTD =
TOTALYTD (
    [Total Sales],
    DATESBETWEEN ( Sales[Date], DATE ( 2015, 11, 01 ), DATE ( 2016, 12, 31 ) )
)

You need to adjust the date part to be affect by your slicers but without further information regarding the tipe of slice and dice you wan to do I can't give you a better explanation.

 

Regards,

MFelix

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

@MFelix Thanks for the response. 

 

I'm looking to compare last year and 2 years prior to the current year's YTD.   So for 2016, the two extra months (Nov and Dec 2015) plus all the months up to September.  I tried your suggested measure, but I wasn't able to get it to work correctly.  After more searching and trial and error, I was able to get the correct YTD amounts for 2016 with the following measure: 

 

LY YTD =
VAR MaxDate =
               CALCULATE ( MAX ( Sales[Date] ), ALL ( Sales ) )
RETURN
               CALCULATE (
                              [LY Total],
                              SAMEPERIODLASTYEAR (
                                  INTERSECT (
                                  VALUES ( Dates[Date] ),
                                  DATESBETWEEN ( Dates[Date].[Date], BLANK (), MaxDate ))))

 

Where [LY Total] is the total sales amount for 2016.  Now, I'm having trouble with 2 years prior YTD.  I'm just getting the total amounts for the entire year 2015 instead of the YTD.   I haven't been successful in getting the right dates filter.  I'd like it to look something like this

 

Test.PNG

 

Hi @Anonymous,

 

You can try to create a measure like below: 

 

L2Y  = CALCULATE(SUM(Sales[Amount]),DATEADD(DATESYTD('Dates'[Date]),-2,YEAR))

 

If it doesn't work, please share the pbix file with dummy data for us to test. 

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Just wanted to say thanks for this formula
I was struggling to get a previous YTD and this helped me a treat

YTD PY = CALCULATE(SUM(MonthlyData[Values]),DATEADD(DATESYTD('Date'[Date]),-1,YEAR))

Hi @Anonymous

 

Couple of questions.

 

1. Just like the measure [LY Total] have you calculated any measure for 2 years total.

2. Do you have a calendar table and linked with your Sales Fact table.

3. Going forward what is your financial year. Will it be from Nov to Oct in the future.

 

I feel you will have to write specific YTD totals for the years 2016 and 2015 as they do not fall under the same financial period.

 

If you can share the pbix please load it on one drive and provide the link here to formulate a solution.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Anonymous
Not applicable

Hi @CheenuSing@v-qiuyu-msft

 

Thanks for your reply.  Yes, I have created a measure for the total from 2 years ago and created a calendar table that's linked to the Sales table.  This year's financial year will match the calendar year (Jan - Dec).  

 

Here's a link to a test pbix 

 

 

Hi @Anonymous,

 

 

A quick question.  Your financial years are different for each year. Nov - Oct 2015 (12 Months),  Nov-Dec 16 (14 Months) and Jan-Dec 2017.

 

For Profit & Loss, Balance Sheet one may have to consolidate the 12 monhts, 14 months and so on for different financial years.


For Sales Reporting , when the current year is reproted for Jan - Sep 2017 - for 9 months. It would be wrong to compare Nov2015 to Sep 2016 which would be 11 months.  One should compare the same number of months in the previous year Jan-Sep 16.

Likewise for Jan-Sep 2015.  Otherwise comparisons reflect different number of months.

 

Please clarify.

 

Cheers

 

CheenuSing

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Anonymous
Not applicable

@CheenuSing

 

How would you go about cutting down the months and getting the ytd numbers? Also,  could you explain how this 

 

LY YTD =
VAR MaxDate =
               CALCULATE ( MAX ( Sales[Date] ), ALL ( Sales ) )
RETURN
               CALCULATE (
                              [LY Total],
                              SAMEPERIODLASTYEAR (
                                  INTERSECT (
                                  VALUES ( Dates[Date] ),
                                  DATESBETWEEN ( Dates[Date].[Date], BLANK (), MaxDate ))))

works with the 14 month year and is it possible to manipulate this to work with 2 years back? 

Hi @Anonymous,

 

I downloaded the YTD_test pbix sent by you.

 

The approach I had taken is as under.

 

1.  I changed your Dates table formation as  -  Dates = CALENDAR(MIN(Sales[Date]), MAX(Sales[Date]))

     This is the best way to create a calendar table based on the minimum and maximum date of the fact tables.

 

2.  Created a column called MonthNumber = Month(Dates[Date])

3.  Set the MonthName to sorted by column MonthNumber.

4.  Created a MonthSequentialNumber column

     MonthSequentialNumber = ('Dates'[Year] - MIN( 'Dates'[Year] )) * 12 + 'Dates'[MonthNumber]

    This generates a sequential number for each month and incremented by 1 for every month in the Dates table.

    In the sample file this number ranges from 11 to 45.

    To go back 1 year just need to subtract 12 from this. Similarly to go back 2 years subtract by 24 and so on.

 

5.  Created a measure called

     YTD1YearB4 = Calculate([Total Sales],Filter(ALL(Dates), Dates[Year] = Max(Dates[Year]) - 1  && Dates 

                                          [MonthSequentialNumber] <= Max(Dates[MonthSequentialNumber]) - 12) )

 

6. Created a measure called

    YTD2YearB4 = Calculate([Total Sales],Filter(ALL(Dates), Dates[Year] = Max(Dates[Year]) - 2  && Dates

                                                    [MonthSequentialNumber] <= Max(Dates[MonthSequentialNumber]) - 24) )

 

Depending on the Year selected it will report the 1year before and 2 year before total sales.

 

I have  uploaded the file in one drive. and the link is

 

https://1drv.ms/u/s!ApP3mBZyGaHfzyeQc9QxgrROjsFD

 

If this works for you please accept this as a solution and also give KUDOS.

 

Cheers

 

CheenuSing

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Hi CheenuSing,

 

I searched for hours on how to figure out how to do YTD LY, YTDL2, L3 years ago and used this calculation and it is great for month end.  But how do I get it to work for a YTD in the middle of the month?  YTD 2023 works summing up through 3/11/23 but YTD 2022, YTD 2021, YTD 2020 and YTD 2019 sums up all the weeks through the end of March even though we are only through the middle of March.  I would like YTD 2022 to be as of 3/12/22, YTD 2021 as of 3/10/21, etc.

 

BUT Amazing formula!!!

 

Hi @krider71 ,

I have created new measures to take care of your reequirements.

 

The code is as under

YTD 1 YearB4 Adj =
VAR MaxDate = MAX ('Dates'[Date])
VAR MAXYEAR = YEAR(MaxDate)
VAR MAXMONTH = MONTH(MAxDate)
VAR MAXDAY = DAY(MaxDate)
VAR PrevYear = MAXYEAR - 1
VAR LeapYear = MOD(PrevYear ,4)
VAR ADjMAxDay = IF (LeapYear = 0 && MAXDAY = 28,29, MAXDAY)
VAR EndDate = DATE(PrevYear, MAXMONTH,ADjMAxDay)
VAR StartDateYear = DATE(PrevYear, 1,1)
Return
Calculate([Total Sales], DATESBETWEEN('Dates'[Date],StartDateYear,EndDate))

For 2 years before
YTD 2 YearB4 Adj =
VAR MaxDate = MAX ('Dates'[Date])
VAR MAXYEAR = YEAR(MaxDate)
VAR MAXMONTH = MONTH(MAxDate)
VAR MAXDAY = DAY(MaxDate)
VAR PrevYear = MAXYEAR - 2
VAR LeapYear = MOD(PrevYear ,4)
VAR ADjMAxDay = IF (LeapYear = 0 && MAXDAY = 28,29, MAXDAY)
VAR EndDate = DATE(PrevYear, MAXMONTH,ADjMAxDay)
VAR StartDateYear = DATE(PrevYear, 1,1)
Return
Calculate([Total Sales], DATESBETWEEN('Dates'[Date],StartDateYear,EndDate))

The code also takes care of February for leap year. If the previous year is leap year it will point to 29 Feb when the max date for current non leap year is 28 February.
You can decide how you want to treat this. 

The data is only upto Sep 11, 2017.
 
Cheers
CheenuSing
 
 



 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Thank you!  I thought I responded and with some additional questions but not sure where that post went?  Anyways, That pretty much worked  - you are amazing -  the issue I keep having is when years start and end.  For Years before and same issue I have in other Powerbi sametimeperiod last year it is using YTD 12/26/21-3/5/22 to compare to 202

YTD 1/1/23-3/11/23.   VS I want it to be YTD 1/2/22-3/12/22 to compare to the 2023

 

Example:

krider71_0-1680281995928.png

 

This is what powerbi is showing as my week end dates and Week # of Year.  It has week 10 as 3/11/23 and then 3/5/22. I want week 10 to be 3/12/22.  Its that week 53 thing and they only way I can fix for comparing TY vs LY is not use sametimeperiod but a formula like: 

CALCULATE(SUM(WAGVT[Total Sales Amount]),dateadd('Dates'[Date],-364,Day))
Week End DateWeek of Year
3/11/202310
3/4/20239
2/25/20238
2/18/20237
2/11/20236
2/4/20235
1/28/20234
1/21/20233
1/14/20232
1/7/20231
12/31/202253
3/12/202211
3/5/202210
2/26/20229
2/19/20228
2/12/20227
2/5/20226
1/29/20225
1/22/20224
1/15/20223
1/8/20222
1/1/20221
12/25/202152

 

Thank you for any help!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.