cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
elf_ranger91
Frequent Visitor

TOTALYTD incorrect values

Hi guys I hope someone can help me out here.

 

I´m having some difficulty with the TOTALYTD function. Here is what I have written in my DAX formula: 

 

YTD Revenue FY16 CLP = TOTALYTD(SUM('FY16 Journal Details'[Monto FY16 CLP]);'FY16 Journal Details'[Effective Date];'Account FY16'[ISG1]="REVENUE";ALL('FY16 Journal Details'[Effective Date]))

 

However, when I put YTD Revenue in a table, I do not get cumulative ytd values. Actually they are the same as the monthly revenue values. Furthermore, the total amount is incorrect (it should be the year total no? the same as total revenue). Here is the table below:

 

powerbitable.png

What´s going on here?

 

Note: the reason that October is first is because that is when our fiscal year begins.  

 

Thanks

 

8 REPLIES 8
Super User IV
Super User IV

Re: TOTALYTD incorrect values

If I am breaking down your formula correctly:

 

YTD Revenue FY16 CLP =

 

TOTALYTD(SUM('FY16 Journal Details'[Monto FY16 CLP]); - So the first parameter you are telling it to SUM your [Monto FY16 CLP] field. That's fine.

 

'FY16 Journal Details'[Effective Date]; - Here you are passing in a column of dates. That's fine.

 

'Account FY16'[ISG1]="REVENUE"; - Now you have passed in a filter to only include columns that are REVENUE. This should be fine as long as the tables are related. In theory, somehow your Account table is related to your FY16 Journal Details so you should only be pulling FY16 Journal Details that relate back to an Account that is tagged as "REVENUE", correct?

 

ALL('FY16 Journal Details'[Effective Date])) - This one I don't understand, the fourth parameter should be the specification of an End Date for the year. It looks like instead you are passing it in an entire column of dates which means it will probably just grab the first date.

 

 


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

Not the Power BI thought police...

I have 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 Datanaut!

Moderator v-qiuyu-msft
Moderator

Re: TOTALYTD incorrect values

Hi @elf_ranger91,

 

In your scenario, please check the syntax of TOTALYTD() function firstly:

 

TOTALYTD(<expression>,<dates>[,<filter>][,<year_end_date>]) 

 

The fourth parameter needs to be “A literal string with a date that defines the year-end date. The default is December 31.” You can try the modify the DAX like below:

 

YTD Revenue FY16 CLP = TOTALYTD(SUM('FY16 Journal Details'[Monto FY16 CLP]);'FY16 Journal Details'[Effective Date];'Account FY16'[ISG1]="REVENUE";"9/30")

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Moderator v-qiuyu-msft
Moderator

Re: TOTALYTD incorrect values

Hi @elf_ranger91,

 

Is the issue solved? Can you check whether @elf_ranger91 and @Greg_Deckler's suggestion is working?

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
danielH
Frequent Visitor

Re: TOTALYTD incorrect values

I have the same issue. 

what is weird is that a have a tile which works, and in the other tile doesnt. with basically same input, diferent Data sources. 

is there a solution for this ? Capture.PNG

 

 

Sean Community Champion
Community Champion

Re: TOTALYTD incorrect values

@danielH

Can you post the formula of the Measure you are using?

Are you specifying the fiscal year end? (as mentioned above)

For example if your FY ends June 30

MEASURE 1

YTD Total =
TOTALYTD ( SUM ( Table[Column] ), 'CalendarTable'[Date], "06/30" )

or alternatively

MEASURE 2

YTD Total ALT =
CALCULATE ( SUM ( Table[Column] ), DATESYTD ( 'CalendarTable'[Date], "06/30" ) )

 

Good Luck! Smiley Happy

danielH
Frequent Visitor

Re: TOTALYTD incorrect values

Sure, I am using for the first one ( the one that comes out correct ) : 

 

YTD USERS = TOTALYTD(SUM('Planner LAN International'[User]);'Planner LAN International'[Enddatuminkl. First Day Support])

 

the second one ( the one woth the error )  : 

 

T YDT Users = TOTALYTD(SUM(LOCATION_LIST_ScheduleDates[Users]);LOCATION_LIST_ScheduleDates[LAN planned end])

 

To answer your quiestion, no i am not using specifying a year end. but if i do specify one, as your example 

 

T YDT Users = TOTALYTD(SUM(LOCATION_LIST_ScheduleDates[Users]);LOCATION_LIST_ScheduleDates[LAN planned end];"06/30")

 

i do see a change at the result, yet again instead of 230 is 2528.

i am compleatly lost, i have no ide what i am missing.

 

thanks, one step closer but not quite there yet. 

Sean Community Champion
Community Champion

Re: TOTALYTD incorrect values

@danielH

 

Looking at the picture you posted above it seems in both tables the Measures work as intended.

 

Can you explain and show what goes wrong and where? What results do you expect?

danielH
Frequent Visitor

Re: TOTALYTD incorrect values

Yeah it took me a while to see it but now i understand. Smiley SurprisedSmiley Surprised

 

looking properly at the results the first figure seems fine since all of the data is on 2017. 

 

now on the second figure, i have data from 2016,2017,2018.

                  The total of the second figure is the YTD value of the date 2018. not the total of the complete data

                  i am obtaining YTD values per year, which is actually correct as you say. i guess what i am looking for is added values of                     the complete dataset not just per year.

 

therefore a Line chart for example goes up and in every year end down then up again. 

Capture.PNG

 

 

 

 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors