cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vincentpit
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:

Knipsel2.PNG

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

 

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

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User IV
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])
)

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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

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?


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

 

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

View solution in original post

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

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.

secondImage

Experience what’s next for Power BI

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