Dynamic Measure depends on slicer value

[Anonymous]

Hi Power BI community,

I developed a measure column for calculating a cumulative data as per below code:

Cumulative Actual MH  =
 CALCULATE (sum ( 'PBI LabourCode_Actual'[Project Job numberMH] ),
 FILTER 
(ALL ( 'PBI LabourCode_Actual')
,'PBI LabourCode_Actual'[WorkDate] <= MAX ( 'PBI LabourCode_Actual'[WorkDate] )))

I want this measure to be depended to a slicer selection for Job Numbers (

PBI LabourCode_Actual'[Project Job numberMH])

, but so far I couldnt find anything to work . any suggestion? the job number values and measure column are  all in a same table. 

Really appreciate your help

[d_gosbell]

The probem is that you are using ALL('PBI LabourCode_Actual') which will remove all filters including slicers. If you change your expression just to do ALL over the WorkDate column it should still calculate correctly, but also work with your Job Number slicer.

Cumulative Actual MH  =
 CALCULATE (sum ( 'PBI LabourCode_Actual'[Project Job numberMH] ),
 FILTER 
(ALL ( 'PBI LabourCode_Actual'[WorkDate])
,'PBI LabourCode_Actual'[WorkDate] <= MAX ( 'PBI LabourCode_Actual'[WorkDate] )))

[Anonymous]

I applied your formula, but it didnt return the correct value. It returnes the Actual Manhour and not its cumulative  ! Look at the below table. I named your formula as Cumulative actual - New

[d_gosbell]

What's that "Date" column on the rows? Is that WorkDate or some other date?

You could try using ALLEXCEPT and excluding the column you are using for your slicer? (is it really [Project Job numberMH] as it seems strange to try to sum and filter by the one column?)

Cumulative Actual MH  =
 CALCULATE (sum ( 'PBI LabourCode_Actual'[Project Job numberMH] ),
 FILTER 
(ALLEXCEPT ( 'PBI LabourCode_Actual', 'PBI LabourCode_Actual'[Project Job numberMH])
,'PBI LabourCode_Actual'[WorkDate] <= MAX ( 'PBI LabourCode_Actual'[WorkDate] )))

This is the problem with having all your data in one big table. Having a separate 'Date' table makes things like cumulative sums a lot easier.

[Anonymous]

Ok, I think I know what the issue is . The Date" column on the rows is not WorkDate. Actually it is date from a calendar table that I created and made a relationship between it and WorkDate. I changed the rows to WorkDate and the formula is working now.

However, for some reason I want to use calendar date for columns not WorkDate. any suggestion?

[d_gosbell · Accepted solution]

If you have a one to many relationship between Date and WorkDate (so the arrow is pointing from Date to WorkDate) you should be able to use the Date column to filter the 'PBI LabourCode_Actual' table. 

In fact the typically pattern for building a cumulative "life to date" measure would be to use your date table in the filter.

eg.

Cumulative Actual MH =
CALCULATE (sum ( 'PBI LabourCode_Actual'[Project Job numberMH] ),
FILTER (ALL ( 'Date')
,'Date'[Date] <= MAX ( 'Date'[Date] )))

Then slicers on any other tables should work normally.