Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
What´s going on here?
Note: the reason that October is first is because that is when our fiscal year begins.
Thanks
Hi @elf_ranger91,
Is the issue solved? Can you check whether @elf_ranger91 and @Greg_Deckler's suggestion is working?
Best Regards,
Qiuyun Yu
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 ?
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!
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.
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.
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |