Forum Discussion
Need help with syntax - adding a filter when calculating a sum please
Hi,
This measure works as intended - it shows all costs for a specific program :
Total Costs Program1 =
CALCULATE (
SUM(DataTable[Cost])+0,
FILTER ( ALL ( DataTable ), DataTable[Program]= "Program1" && DataTable[Mode] = "Internal" )
)
But i cannot work out the correct syntax to add the filter statement above into the measure below:
The measure currently returns costs for the previous year for all programs but I would like to apply the criteria shown to select the previous years costs for Program1 only.
Total Costs Program1 (For Previous Year) =
CALCULATE(SUM(DataTable[Cost]),DATESYTD(ENDOFYEAR(DATEADD('Calendar'[Date],-1,YEAR)),"12,31"))
I'm assuming the
FILTER ( ALL ( DataTable ), DataTable[Program]= "Program1" && DataTable[Mode] = "Internal" )
should be incorporated into the immediately after the SUM( in the 'Total Costs Program1 (For Previous Year)' measure but am struggling to get the correct syntax.
Thanks,
Richard
RichardJ You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008Turned out the solution was pretty simple in the end.
Total Costs Program1 = CALCULATE ( SUM(DataTable[Cost])+0, FILTER ( ALL ( DataTable ), DataTable[Program]= "Program1" && DataTable[Mode] = "Internal" ), PREVIOUSYEAR('Calendar'[Date]) )
3 Replies
RichardJ You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008Thanks for the assistance Greg_Deckler . Thats Time Intelligence you provided were very helpful.
Turned out the solution was pretty simple in the end.
Total Costs Program1 = CALCULATE ( SUM(DataTable[Cost])+0, FILTER ( ALL ( DataTable ), DataTable[Program]= "Program1" && DataTable[Mode] = "Internal" ), PREVIOUSYEAR('Calendar'[Date]) )