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
Lovebo01
Helper I
Helper I

TotalYTD with start date and end date

Hello everyone,

 

I am looking for a TotalYTD() function with a start date and an end date.

I tried with a Flag_Start_Date with some If but I wasn't able to find the answer.

 

The Start date should be always "01/09"

And the End date should be the last date (DD/MM) of data.

 

For example If I have some data from 01/01/2015 to 01/02/2017 (DD/MM/YYYY format)

I want be build a table visualization like this : 

 

YearQtrSum SalesYTD Sales fiscal
201511010
2015210 
201531010
201541020
201612040
2016220 
201632020
201642040
201713070
2017230 
201733030
201743060

 

I don't add Month column to not have to many rows in my table.

Is it possible ?

 

Thank you for your help,

Regards,

Lovebo

4 REPLIES 4
Lovebo01
Helper I
Helper I

Hello Stachu,

 

Yes of course : 

 

YearQtrMonthSales
20153710
20153810
20153910
201541010
201541110
201541210
20161120
20161220
20161320
20162420
20162520
20162620
20163720
20163820
20163920
201641020
201641120
201641220
20171130
20171230

 

 

Stachu
Community Champion
Community Champion

I cannot reconcile numbers with your example - can you check the attached file?
I added Date column to the table with data, as well as Calendar table so we can make use of the time intelligence functions.
Then I added the following measure

Measure = CALCULATE(SUM('Table'[Sales]),DATESYTD('Calendar'[Date],"31/08"))

It works as I would expect with this data, but the results don't seem to be consistent with your expected output - does the input match the output you posted in the first post?
Capture.PNG

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Hello Stachu,

 

Thank you for your answer, in fact I need to add a start date and an end date. With the classic DatesYTD() or TotalYTD() functions we can only add an end date which is also the start date (D+1) in fact.

 

So for example my start date should be 1st february (01/02/2015) and me end date 31st August (31/08/2017).

So with the data that I sent, we should have this result : 

 

ExampleYTDwithStartEndDates.JPG

 

Thank you again for your help,

Lovebo

 

Stachu
Community Champion
Community Champion

Can you add sample input tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).

Column1 Column2
A 1
B 2.5

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors