cancel
Showing results for
Did you mean:
Frequent Visitor

## Get the YTD formula to work

Hello,

I'm a beginner at Power BI and i'm trying to get the YTD measure to work, but i can't seem to get it right..

What i did is:

• I have an invoice tables INVOICES containing invoices since 1-1-15 with the column [TOTAAL] als invoice total in eu.
• Add a calender table DATES (start 1-1-2015 end =now()
• Create a relation between DATES[DATE] & INVOICES[DATE]
• Use the following formula:
YTD = CALCULATE(sum(INVOICES[TOTAAL]);DATESYTD(DATES[DATE]))

I get the following result:

The problem i get is that every year sums up the total revenue (2015 & 2016 till 31-12), but i need the revenue 2015 & 2016 up until today (in this case 30-10

Who can help?

Vincent

1 ACCEPTED SOLUTION
Resolver V

```ColumnYTD =
VAR Day1 =
DAY ( NOW () )
VAR Month1 =
MONTH ( NOW () )
VAR Year1 =
MAX ( DATUM[Year] )
RETURN
CALCULATE (
SUM ( FACTUREN[TOTAAL] ),
AND (
DATUM[Date] >= DATE ( Year1, 1, 1 ),
DATUM[Date] <= DATE ( Year1, Month1, Day1 )
)
)```
6 REPLIES 6
Super User IV

Take a look at TOTALYTD:

https://msdn.microsoft.com/en-us/library/ee634400.aspx

This is what the quick measure brings back:

```Column YTD =
IF(
ISFILTERED('Calendar'[Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
TOTALYTD(SUM('Calendar'[Column]), 'Calendar'[Date].[Date])
)```

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Frequent Visitor

Hi smoupre,

Thanks for the quick response.

I updated the formula to the folowing, still no result..

Column YTD = TOTALYTD(sum(FACTUREN[TOTAAL]);DATESYTD(DATUM[Date];"30/12"))

I uploaded my file to dropbox.

https://www.dropbox.com/s/7516rxl7jqnp0kd/Testfile%20community.pbix?dl=0

Can you help?

Thanks!

Vincent

Super User IV

```TOTAAL YTD =
IF(
ISFILTERED('DATUM'[Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
TOTALYTD(SUM('FACTUREN'[TOTAAL]), 'DATUM'[Date].[Date])
)```

Is \$12,796,464.03 not the right answer for 2017?

It seems like the right answer as I created a slicer and set it to 1/1/2017 - 10/30/2017 and it returned the same answer. Is the issue that the previous years are not returning the total for the same time period but rather the entire year?

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Frequent Visitor

The right answer for 2017 is correct \$12,796,464.03

I want to compare this revenue to the other years same period revenue.

revenue 2015 1-1 till 30-10

revenue 2016 1-1 till 30-10

If i filter on date the returned values should be:

2015: \$11,571,369.09

2016 :\$12,381,482.41

at this moment the returned values are revenues for the whole 2015 and 2016 year.

Edit: When i use the formula you handed me i get no values in 2015 & 2016.

Resolver V

```ColumnYTD =
VAR Day1 =
DAY ( NOW () )
VAR Month1 =
MONTH ( NOW () )
VAR Year1 =
MAX ( DATUM[Year] )
RETURN
CALCULATE (
SUM ( FACTUREN[TOTAAL] ),
AND (
DATUM[Date] >= DATE ( Year1, 1, 1 ),
DATUM[Date] <= DATE ( Year1, Month1, Day1 )
)
)```
Frequent Visitor

This does work, great! Thanks for all your help.

Announcements

#### The largest Power BI virtual conference

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

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors