YTD example

Question

Hi everibody.I have the following measureYTD Capacity Hours = CALCULATE( TOTALYTD( SUM(Capacity[capacity]) , Dates[Date] ) , &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ALL(Dates) ,&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;FILTER( Capacity, Capacity[idemployee] &lt; "30000"&nbsp; &amp;&amp; Capacity[functional_type] = "Consulting"&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;)table Capacity (date, capacity, idemployee, functiona_type,......)I hope to receive the sum of capacity for the last year load into the table (exmpl loaded up to 31-03-2017)&nbsp;I have defined a time slicer on Dates (i can select differents months between jan 2016 and march 2017)&nbsp; the results vary on selection.&nbsp;How can help methnks in advancedavid

v-yulgu-msft · Accepted Answer

Hi&nbsp;davaru,
&nbsp;
If the date slicer is set to "Between", and the date column is dragged from calendar table, you can refer to below formulas.
Dates&nbsp;=
CALENDAR&nbsp;(&nbsp;MIN&nbsp;(&nbsp;Capacity[date]&nbsp;),&nbsp;MAX&nbsp;(&nbsp;Capacity[date]&nbsp;)&nbsp;)

YTD&nbsp;Capacity&nbsp;=
CALCULATE&nbsp;(
&nbsp;&nbsp;&nbsp;&nbsp;TOTALYTD&nbsp;(&nbsp;SUM&nbsp;(&nbsp;'Capacity'[capacity]&nbsp;),&nbsp;'Dates'[Date]&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;ALL&nbsp;(&nbsp;'Dates'&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Capacity',
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Capacity'[idemployee]&nbsp;&lt;&nbsp;30000
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;'Capacity'[functional_type]&nbsp;=&nbsp;"Consulting"
&nbsp;&nbsp;&nbsp;&nbsp;)
)

LY&nbsp;YTD&nbsp;Capacity&nbsp;=
CALCULATE&nbsp;(&nbsp;[YTD&nbsp;Capacity&nbsp;Hours],&nbsp;SAMEPERIODLASTYEAR&nbsp;(&nbsp;Dates[Date]&nbsp;)&nbsp;)

&nbsp;
However, if the date slicer is set to "List", you need an extra calendar&nbsp;table. You should drag date column from 'Dates2'.
YTD&nbsp;=
CALCULATE&nbsp;(
&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;Capacity[capacity]&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;DATESYTD&nbsp;(&nbsp;Dates[Date]&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Capacity,
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Capacity[idemployee]&nbsp;&lt;&nbsp;30000
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;Capacity[functional_type]&nbsp;=&nbsp;"Consulting"
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;Capacity[date]&nbsp;&lt;=&nbsp;MAX&nbsp;(&nbsp;Dates2[Date]&nbsp;)
&nbsp;&nbsp;&nbsp;&nbsp;)
)

YTD&nbsp;LY&nbsp;=
CALCULATE&nbsp;(
&nbsp;&nbsp;&nbsp;&nbsp;[YTD],
&nbsp;&nbsp;&nbsp;&nbsp;SAMEPERIODLASTYEAR&nbsp;(
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;INTERSECT&nbsp;(
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VALUES&nbsp;(&nbsp;Dates[Date]&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DATESBETWEEN&nbsp;(&nbsp;Dates[Date],&nbsp;BLANK&nbsp;(),&nbsp;MAX&nbsp;(&nbsp;Dates2[Date]&nbsp;)&nbsp;)
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)
&nbsp;&nbsp;&nbsp;&nbsp;)
)

Best regards,Yuliana Gu

Forum Discussion

YTD example

2 Replies

Related content

Data Agent Example Queries

YTD and YTD PY for multiple measures

Fabric notebook example

Matrix displaying quarterly, YTD, YTD PY, and YOY Var

PowerBI Example with YTD, MDT and QTD slicer or buttons