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.
Good morning, I am having a problem with the TotalYTD formula that is breaking my head, I have to add the total tons sold during the year, it ends on 06/30.
The formula I use for that and that had been working without any problems until December is the following:
TOTALYTD (
SUM ('BT_Sales' [Amount_tons]);
'LU_Date' [Date]. [Date];
"6/30"
)
From the beginning of January 2020, the result of that formula is blank.
Did it happen to you or do you know what it could look like to make it work again
In the meantime I did the following:
IF (
[year]> 2019;
calculate (
SUM ('BT_Sales' [Amount_tons]);
DATESBETWEEN (LU_Date [Date]; DATE (2019; 7; 1); max ('LU_Date' [Date])
));
TOTALYTD (
SUM ('BT_Sales' [Amount_tons]);
'LU_Date' [Date]. [Date];
"6/30"
)
)
here I hit how the year value comes out
year = year (MAX (lu_date [date]))
I will thank you in advance because I could be failing that from January (whatever I put back works well!)
Solved! Go to Solution.
I am sure your are using a calendar table. Formula you given above should work
TOTALYTD (SUM ('BT_Sales' [Amount_tons]);
'LU_Date' [Date]. [Date];
"6/30")
If it is not a time stamp then remove .date. and use calendar date instead of table date.
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.
Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Can you check the max date of your calendar/ date table.
Yes, it's "2021/06/30"
I am sure your are using a calendar table. Formula you given above should work
TOTALYTD (SUM ('BT_Sales' [Amount_tons]);
'LU_Date' [Date]. [Date];
"6/30")
If it is not a time stamp then remove .date. and use calendar date instead of table date.
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.
Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
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 |
---|---|
111 | |
97 | |
82 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |