Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
Solved! Go to 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.
For more details, you could refer to my attachment.
Best Regards,
Cherry
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:
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:
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.
For more details, you could refer to my attachment.
Best Regards,
Cherry
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!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |