Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
TOTALYTD acts as sum for each time period rather than a cumulative.
I have a separate Calendar table in Power Pivot and I created relationship with my main table.
Here is the formula
YTD_Matches:=TOTALYTD(sum(Complete_Detailed_Incidents[Matches]),'Calendar'[Date])
When I create Pivot, below is what it looks like. How to fix this?
Row Labels | YTD_Matches | Sum of Matches |
Jan | 3807632 | 3807632 |
Feb | 1786060 | 1786060 |
Mar | 504310 | 504310 |
Apr | 609969 | 609969 |
May | 1277743 | 1277743 |
Jun | 3527579 | 3527579 |
Jul | 2041383 | 2041383 |
Grand Total | 13554676 | 13554676 |
Solved! Go to Solution.
Hello @nickchobotar @TomMartens @Interkoubess
Thank you all for the quick and helpful responses.
I just realized my Months in Pivot Table was from wrong table. Instead of getting it from Data table (which I named 'Calendar'), I got it from the Facts table.
Below is the updated table
Hello @nickchobotar @TomMartens @Interkoubess
Thank you all for the quick and helpful responses.
I just realized my Months in Pivot Table was from wrong table. Instead of getting it from Data table (which I named 'Calendar'), I got it from the Facts table.
Below is the updated table
Hi @QQQ,
Glad to hear that you have resolved the problem. Would you please kindly mark the helpful response and your resolution as an answer so that others having similar concern can find the solution more easily?
Regards,
Yuliana Gu
Hello@QQQ
Your formula is correct and it should work.
Could you please check 2 things:
1) Is your Calendar table marked as Date Table (Design Tab in Power Pivot)
2) All the dates in your Calendar table must be consecutive dates, no gaps.
N -
Yes, both conditions are true
Hey,
I'm wondering if the column comes from your fact table and not from Calendar table, if this is the case get your date column from your Calendar table.
Your formel is fine, here is an example you may want to look at, have a look at the report page "YTD Startert" the measure "Amount TotylYtd"
Amount TotalYtd = TOTALYTD(SUM('FactWithDates'[Amount]),'Calendar'[Date])
looks pretty much the same than your measure.
Cheers
Hi @QQQ,
Please try YTD_Matches = CALCULATE(sum(Complete_Detailed_Incidents[Matches]),DATESYTD('Calendar'[date]))
And let us know.
Thx.
Thanks for the quick response!
However I found the solution.
The Months should be from Date Table (which I named 'Calendar'). Instead, I originally got the dates from the Fact table.
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 | |
101 | |
84 | |
79 | |
69 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |