Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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

 

9 REPLIES 9
v-qiuyu-msft
Community Support
Community Support

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.

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

@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

Why we need specify the FY end? And what will be the format of the year end? Or why it is should be "06/30"?

Thanks,

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

@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?

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

 

 

 

 

v-qiuyu-msft
Community Support
Community Support

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.
Greg_Deckler
Super User
Super User

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.

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.