Get the YTD formula to work

[vincentpit]

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?

Thanks in advance!

Vincent

[Greg_Deckler]

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])
)

[vincentpit]

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

[Greg_Deckler]

OK, downloaded file. I created the TOTALYTD forumla using this measure:

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?

[vincentpit]

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.

[vik0810 · Accepted solution]

I downloaded your file and used this measure, it works.

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 )
        )
    )

[vincentpit]

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