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.
Hi everibody.
I have the following measure
YTD Capacity Hours = CALCULATE( TOTALYTD( SUM(Capacity[capacity]) , Dates[Date] ) ,
ALL(Dates) ,
FILTER( Capacity, Capacity[idemployee] < "30000" && Capacity[functional_type] = "Consulting" )
)
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)
I have defined a time slicer on Dates (i can select differents months between jan 2016 and march 2017) the results vary on selection.
How can help me
thnks in advance
david
Solved! Go to Solution.
Hi @davaru,
If the date slicer is set to "Between", and the date column is dragged from calendar table, you can refer to below formulas.
Dates = CALENDAR ( MIN ( Capacity[date] ), MAX ( Capacity[date] ) ) YTD Capacity = CALCULATE ( TOTALYTD ( SUM ( 'Capacity'[capacity] ), 'Dates'[Date] ), ALL ( 'Dates' ), FILTER ( 'Capacity', 'Capacity'[idemployee] < 30000 && 'Capacity'[functional_type] = "Consulting" ) ) LY YTD Capacity = CALCULATE ( [YTD Capacity Hours], SAMEPERIODLASTYEAR ( Dates[Date] ) )
However, if the date slicer is set to "List", you need an extra calendar table. You should drag date column from 'Dates2'.
YTD = CALCULATE ( SUM ( Capacity[capacity] ), DATESYTD ( Dates[Date] ), FILTER ( Capacity, Capacity[idemployee] < 30000 && Capacity[functional_type] = "Consulting" && Capacity[date] <= MAX ( Dates2[Date] ) ) ) YTD LY = CALCULATE ( [YTD], SAMEPERIODLASTYEAR ( INTERSECT ( VALUES ( Dates[Date] ), DATESBETWEEN ( Dates[Date], BLANK (), MAX ( Dates2[Date] ) ) ) ) )
Best regards,
Yuliana Gu
Hi @davaru,
If the date slicer is set to "Between", and the date column is dragged from calendar table, you can refer to below formulas.
Dates = CALENDAR ( MIN ( Capacity[date] ), MAX ( Capacity[date] ) ) YTD Capacity = CALCULATE ( TOTALYTD ( SUM ( 'Capacity'[capacity] ), 'Dates'[Date] ), ALL ( 'Dates' ), FILTER ( 'Capacity', 'Capacity'[idemployee] < 30000 && 'Capacity'[functional_type] = "Consulting" ) ) LY YTD Capacity = CALCULATE ( [YTD Capacity Hours], SAMEPERIODLASTYEAR ( Dates[Date] ) )
However, if the date slicer is set to "List", you need an extra calendar table. You should drag date column from 'Dates2'.
YTD = CALCULATE ( SUM ( Capacity[capacity] ), DATESYTD ( Dates[Date] ), FILTER ( Capacity, Capacity[idemployee] < 30000 && Capacity[functional_type] = "Consulting" && Capacity[date] <= MAX ( Dates2[Date] ) ) ) YTD LY = CALCULATE ( [YTD], SAMEPERIODLASTYEAR ( INTERSECT ( VALUES ( Dates[Date] ), DATESBETWEEN ( Dates[Date], BLANK (), MAX ( Dates2[Date] ) ) ) ) )
Best regards,
Yuliana Gu
Thnks.
Good solutions It works
Tnks again
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 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |