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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors