YTD Slicer

Question

Hi All, I have a requirement where i have to have YTD slicer. i.e. if i select June 2025, the table should populate values from Jan-25 till June-25 , if I select feb-24, the table should populate values from Jan-24 till feb-24.

After researching, i ama able to acheive this by creating a duplicte dte table and having a passive relationship and using measure. But i have an issue when I try to use it with other fields in a table. the table just breaks. the moment i add premium and commission in the table

VAR SelectedDate = MAX('Month Accounting'[MonthEndDate])

VAR SelectedYear = YEAR(SelectedDate)

VAR SelectedMonth = MONTH(SelectedDate)

VAR FirstDateOfYear = DATE(SelectedYear, 1, 1)

VAR Result =

CALCULATE(

[Current Company Revenue],

REMOVEFILTERS('Month Accounting'),

'Month Accounting'[MonthEndDate] >= FirstDateOfYear,

'Month Accounting'[MonthEndDate] <= SelectedDate,

USERELATIONSHIP('Date'[MonthEndDate], 'Month Accounting'[MonthEndDate])

)

RETURN Result

ashish_mathur · Answer

Hi,
I have solved a similar problem in the attached file.
Hope this helps.

&nbsp;

jostnachs · Answer

Thank you for sharing this...I tried this too changing my relationship from Month accounting to the fact table. but the behaviour is same the table breaks saying query exceeding resources the moment i use the measure in the table along with other.Commission_UpToSelectedDate1 =VAR SelectedDate = MAX('Month Accounting'[MonthEndDate]) &nbsp;VAR SelectedYear = YEAR(SelectedDate) &nbsp;VAR SelectedMonth = MONTH(SelectedDate) VAR FirstDateOfYear = DATE(SelectedYear, 1, 1) &nbsp;VAR Result =&nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; [Current Company Revenue], &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; REMOVEFILTERS('Month Accounting'), &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; 'Month Accounting'[MonthEndDate] &gt;= FirstDateOfYear, &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; 'Month Accounting'[MonthEndDate] &lt;= SelectedDate, &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; USERELATIONSHIP('Date'[MonthEndDate], _Production[D_AccountingMonth]) &nbsp;&nbsp; &nbsp; )RETURN Result

v-echaithra · Answer

Hi&nbsp;jostnachs&nbsp;,Thank you for the update.&nbsp;
The issue occurs&nbsp;when a&nbsp;visual has attempted to query too much data for the server to complete the result with the available resources.
As suggested in the error, you may need to try filtering the visual to reduce the amount of data in the result currently. there is limit.&nbsp;Apply data-point limits and strategies by visual type&nbsp;
&nbsp;
And you can follow the tips to optimize your report：
1.Do not expose in a view a column that is not necessary in the Power BI data model.
2.Optimize the DAX calculation used in this visual to use less memory and return faster or change what the visual displays.
3.Remove unnecessary precision or split granularity values to reduce cardinality(Significantly reduce highly unique datetime values by splitting the date and time into separate columns.&nbsp;)Reference:&nbsp;Chris Webb's BI Blog: The “Visual Has Exceeded The Available Resources” Error In Power BIIf this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.Thanks,Chaithra E.

v-echaithra · Answer

Hi&nbsp;jostnachs&nbsp;,
May I ask if you have gotten this issue resolved?
&nbsp;
If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.
&nbsp;
Regards,Chaithra.

v-echaithra · Answer

Hi&nbsp;jostnachs&nbsp;,
We wanted to kindly follow up to check if the solution provided&nbsp;for the issue worked? or Let us know if you need any further assistance?If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
&nbsp;
Regards,Chaithra.

Forum Discussion

YTD Slicer

9 Replies

Related content

YTD Slicer

YTD with Slicer

Dynamic YTD with Month slicer

Year, Month, Day and YTD in a single Slicer

YTD with month slicer