How to calculate YTD sales for multiple years (my function didn't work)

[Anonymous]

Hi Everyone,

Happy New Year. I'm hoping if someone could help me with the YTD sales calculation. Last year I created a measure to calculate YTD sales and it worked. Now it's 2024...the data shows weird for the new year.

I have a calendar table and a sales table, linked by date. In the calendar table, I set my week to run from every Thursday to the following Wednesday. In below screenshot, the WeekDate is each week's starting date. As you can see, everything looks fine up until 12/21/2023. I don't know what went wrong in my measure that caused error in the most recent two weeks.

My YTD calculation is: 

YTD Total Sales = TOTALYTD([Total Sales],'* Calendar_PowerQuery'[Date])

Thank you in advance for your help!

[amustafa]

Sorry that I missed that part 😞

here are all the column in the Calendar table. You can create thes one by one.

Year = YEAR(Calendar[Date])

MonthNum = MONTH(Calendar[Date])

MonthName = FORMAT(DATE(1, [MonthNum], 1), "MMM")

WeekDate =

VAR CurrentDate = 'Calendar'[Date]

VAR WeekDayNumber = WEEKDAY(CurrentDate, 1)  // Sunday = 1, ..., Saturday = 7

RETURN

    CurrentDate - IF(WeekDayNumber >= 5, WeekDayNumber - 5, WeekDayNumber + 2)

WeekdayNum = WEEKDAY('Calendar'[Date], 2)

DayName = SWITCH(WEEKDAY('Calendar'[Date], 2), 1, "Sun", 2, "Mon", 3, "Tue", 4, "Wed", 5, "Thu", 6, "Fri", 7, "Sat")

Weekday Name = SWITCH(WEEKDAY('Calendar'[WeekDate], 2), 1, "Sun", 2, "Mon", 3, "Tue", 4, "Wed", 5, "Thu", 6, "Fri", 7, "Sat")

[Anonymous]

Hi @amustafa 

Hope you had a great weekend and thank you for sharing the measures with me! I spent some time over the weekend using your method and was able to replicate what you did. However, I noticed that the YTD cumulative amount only totalled up until the week date 12/28/2023. From the new year starting on week date 4/1/2024, the amount from 2023 was not added to the amount in the weeks in 2024.. that being said, the cumulation in 2024 started brand new...is there a way to modify your measure to fix this issue so that I can have the cumulative amount on a rolling basis across the years? 

This is what I wanted to achieve: as you can see 28/12/2023 amount was added to 4/1/2024 amount

This is what I currently see: the cumulation stopped at week 28/12/2023 and a new YTD started 4/1/2024

Thank you very much for all the help you offered me! 

Kathy

[amustafa]

Alright, first you need to create a new table and paste the following DAX in the formula bar.

Calendar = CALENDARAUTO()

Join this new Calendar table to your Sales table on Date column.

Create new measure column in your Sales table as I mentioned earlier.

[Anonymous]

Hi @amustafa ,

I followed your instruction and created the calendar table, may I ask how you created the weekday column please? Currently the YTD sales are showing by each date based on this calendar.

Thank you!

Kathy

[Anonymous]

Hi @amustafa 

Thank you very very much for being so patient with me!! Let me try and get back to you!

Best regards,

Kathy

[amustafa]

To adjust your YTD (Year-To-Date) calculation in Power BI to align with your custom week configuration (Thursday to Wednesday), you'll need to modify the DAX formula. The standard TOTALYTD function works with the calendar year, starting from January 1st. However, in your scenario, the year might need to start from the first Thursday of the year, which requires a more custom approach.

Here's an adjusted version of your DAX formula:

YTD Total Sales =
CALCULATE(
[Total Sales],
FILTER(
ALL('Calendar_PowerQuery'),
'Calendar_PowerQuery'[Date] <= MAX('Calendar_PowerQuery'[Date]) &&
'Calendar_PowerQuery'[Year] = YEAR(TODAY()) &&
'Calendar_PowerQuery'[CustomYearStart] <= TODAY()
)
)

This formula assumes that your 'Calendar_PowerQuery' table has appropriate columns for 'Year' and 'CustomYearStart'. Adjust the column names as per your actual table schema.

[Anonymous]

Hi @amustafa ,

Thank you very much for taking the time to help me with my question. I really appreciate it! I tried your dax but the outcome was not what I expected (sorry if I didn't outline my question clearly). Below is the two columns of the calendar table in my Power BI. I help my team report the total sales on a weekly basis and starting from every Thursday to the following Wednesday. In the visual, I want the x-asix to be 'WeekDate'. In my Calendar table, I have the 'WeekDate' Column showing the date of each Thursday. Do you mind showing me how to modify your Dax so that I can get the YTD sales on each WeekDate please? To clarify, the YTD Sales in my case shows the cumulative total on each of the WeekDate.

Below are the desired outcome in table format and chart format I created in excel but would like to show them in Power bi:

Thank you!

[amustafa]

Hi @Anonymous 

Sorry, didn't understood your problem at first. I replicated your scenerio and sharing how to solve it. See sample .pbix with sample data in my shared folder:

YTD Weekday Sales

Here' the DAX I used based on sample. See how I created a Calendat table and Weekday column and joined it to Sales table. 

YTD Total Sales =

VAR CurrentDate = MAX('Calendar'[Date])

VAR CurrentYear = YEAR(CurrentDate)

VAR FirstDayOfYear = DATE(CurrentYear, 1, 1)

VAR WeekdayFirstDay = WEEKDAY(FirstDayOfYear, 2)

VAR FirstThursdayOffset = IF(WeekdayFirstDay > 4, 11 - WeekdayFirstDay, 4 - WeekdayFirstDay)

VAR FirstThursdayOfYear = FirstDayOfYear + FirstThursdayOffset

VAR CurrentFiscalYearStart = IF(CurrentDate < FirstThursdayOfYear, FirstThursdayOfYear - 364, FirstThursdayOfYear)

RETURN

    CALCULATE(

        SUM('MySales'[Sales]),

        FILTER(

            ALL('Calendar'),

            'Calendar'[Date] >= CurrentFiscalYearStart &&

            'Calendar'[Date] <= CurrentDate

        )

    )

[Anonymous]

Hi @amustafa,

I'm very sorry for the late reply. Unfortunately I can't use onedrive on my company laptop..Do you mind taking a screenshot of how the calendar table looks like please?

Thank you!