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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
wverheijen
Frequent Visitor

YTD of years until today

Hi! I'm looking to edit the TotalYTD function to show only the total of the period from the start of the year until today, but then copied in different years and compared tot the previous year.This would be:

 

date                                                     Sales     Diff

2016 (01/01/2016 - 06/18/2016)           1000

2017 (01/01/2017 - 06/18/2017)           2000    200%

2018 (01/01/2018 - 06/18/2018)           1000    50%

 

Can anoyone help me achieve this?

1 ACCEPTED SOLUTION

Hi @wverheijen

 

I have made a test with your scenario to calculate the YTD sales between the period you required.

 

I created a Calendar Table with CALENDARAUTO and created one calculated column Today with the formula below, then I create the relationship with the two tables.

 

 

Today = DATE(YEAR('Table'[Date]),MONTH(TODAY()),DAY(TODAY()))

 

Besides, I created two measures to calculate the YTD.

 

The first formula is to calculate the YTD between the period.

Measure =
TOTALYTD (
    SUM ( 'Sheet22'[Sales] ),
    'Table'[Date],
    FILTER ( ALL ( 'Table'[Date] ), 'Table'[Date] <= MAX ( 'Table'[Today] ) )
)

Then we need to create another measure by if to determine the date.

 

Measure 3 = 
IF(MAX('Table'[Date])<=MAX('Table'[Today]),[Measure],BLANK())

Here is my test result.

 

Untitled.png

 

For more details, you could refer to my attachment.

 

Best Regards,

Cherry

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

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

What does your original data look like and do you have a date table?

 

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

Also, you might want to check out my time intelligence quick measure here:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Thanks for your reply! I've been working with the Time Intelligence function that you sent, this has helped me calculate the difference from years before. Yet I am facing one more problem.

this is my current result:

table.png

I need the Total_YTD column to calculate total from the start of the year until today's date. this is my DAX code:

Total_YTD = TOTALYTD(SUM(Factuurregels[Regelbedrag]);Factuurregels[Datum].[Date];
                    FILTER(Factuurregels;
                        Factuurregels[Datum].[MonthNo] <= MONTH(TODAY()) 
                        && Factuurregels[Datum].[Day] <= DAY(TODAY())
                        )
            )

2 issues appear here:

1. The values don't add up

2. it's calculating dates 1 till today for every month,  I want to see the YTD value of the full months until today's date. Wish I could just apply this: 

TOTALYTD(SUM(Factuurregels[Regelbedrag]);Factuurregels[Datum].[Date];ALL(Factuurregels[Datum].[Date];TODAY())

Hope someone can help me! 

Hi @wverheijen

 

I have made a test with your scenario to calculate the YTD sales between the period you required.

 

I created a Calendar Table with CALENDARAUTO and created one calculated column Today with the formula below, then I create the relationship with the two tables.

 

 

Today = DATE(YEAR('Table'[Date]),MONTH(TODAY()),DAY(TODAY()))

 

Besides, I created two measures to calculate the YTD.

 

The first formula is to calculate the YTD between the period.

Measure =
TOTALYTD (
    SUM ( 'Sheet22'[Sales] ),
    'Table'[Date],
    FILTER ( ALL ( 'Table'[Date] ), 'Table'[Date] <= MAX ( 'Table'[Today] ) )
)

Then we need to create another measure by if to determine the date.

 

Measure 3 = 
IF(MAX('Table'[Date])<=MAX('Table'[Today]),[Measure],BLANK())

Here is my test result.

 

Untitled.png

 

For more details, you could refer to my attachment.

 

Best Regards,

Cherry

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

@v-piga-msft thanks a lot for your solution! very very useful! 😀

Anonymous
Not applicable

Sorry but in your example the total field is blank! How can i show the total value? (in your example should be 18721 for 2016).

Thank you!

 

Amazing! This is exactly what I needed and it works perfectly!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors