cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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 🙂

Proud to be a Super User!

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 🙂

Proud to be a Super User!

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors