Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
My fiscal year starts from April-March
I wanted to create TOTALYTD for my sales depending upon my fiscal year
Sample requirement
fiscal Year Sales TotalYTD
2019-Q1 5 5
2019-Q2 2 7
2019-Q3 1 8
2019-Q4 2 10
2020-Q1 4 4
2020-Q2 3 7
And so on till my current fiscal QTR which is FY21-Q1
Total YTD sum = TOTALYTD([Sum],derived_dimdate[fulldate])
derived_dimdate is a date table.
My FY19,20,21 Q4 is being treated as the Q1 and giving me the wrong output.
It should start calculating fresh from FY19-Q1, FY20-Q1, FY21-Q1 till my current fiscal year qtr which is FY21-Q1
Solved! Go to Solution.
@Nimai123 , In case your year end at 3/31.
Please give like
Total YTD sum = TOTALYTD([Sum],derived_dimdate[fulldate],"3/31")
Other examples
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((Table[Date]),"3/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR(Table[Date])),"3/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd(Table[Date],-1,Year),"3/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd(Table[Date],-1,Year)),"3/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd(Table[Date],-2,Year),"3/31"))
@Nimai123 , In case your year end at 3/31.
Please give like
Total YTD sum = TOTALYTD([Sum],derived_dimdate[fulldate],"3/31")
Other examples
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((Table[Date]),"3/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR(Table[Date])),"3/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd(Table[Date],-1,Year),"3/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd(Table[Date],-1,Year)),"3/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd(Table[Date],-2,Year),"3/31"))
Hello,
Perhaps you need to use the fourth parameter of
https://docs.microsoft.com/en-us/dax/totalytd-function-dax
year_end_date | (optional) A literal string with a date that defines the year-end date. The default is December 31. |
Hope this helps
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 |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |