Use Slicer selection in calculated column (Parameter)

1
votes

I have: - a slicer with dates and a table 'Log' with a column 'Date' with dates and a column 'Max' with numbers (e.g. 0,31)

I need to calculate the average of all values in the column 'Max' when the date is before the slicer selection date - and an average for after.

End result: a Card visualisation with the average of all values before the selected date and a Card visualisation with the average of all values after the selected date.

2
powerbidax
I think you don't need the calculated column. In you question says you have two, both called Log. Add some sample data and your expected result – alejandro zuleta

2 Answers

2
votes

Create three measures:

avg = AVERAGE('Log'[Max])



AverageAfterSelectedDate =
CALCULATE (
    [avg],
    FILTER ( ALL ( 'Log' ), [Date] > MINX ( 'Log', 'Log'[Date] ) )
)



AverageBeforeSelectedDate =
CALCULATE (
    [avg],
    FILTER ( ALL ( 'Log' ), 'Log'[Date] < MINX ( 'Log', [Date] ) )
)

Just use the AverageBeforeSelectedDate and AverageAfterSelectedDate measures in the cards.

For this data:

enter image description here

It produces:

enter image description here

Let me know if this helps.

0
votes

I had to calculate count of all Accounts that were opened before the selected Slicer Date.

Here is what I ended up doing

TotalAccounts = 
CALCULATE(
      DISTINCTCOUNT(Table1.ID),
           FILTER( 
                    ALL(Table1),MIN(Table2.Date)>Table1.Date)
                 )