Keep filter context in date slicer but suspend in table (Is it possible?)

DAX DAX Calculations
1

Hi All,

I have a scenario where I am trying to calculate the average price of an item within given dates based on a slicer date. The issue I have is that when I put the date on a table I want the average price to be calculated based on the max and min of the slicer selections. So the filter context when the dates are on a table need to be somehow suspended and only consider the dates based on the slicer. This is what I have tried below but it is not giving me the correct answer I want when I put in in a table.

OrgT Avg P =
VAR OrgTMinPrice = MIN(‘1inchPolygonOrigin’[Origin Token])
VAR AveragePrice = AVERAGEX(
VALUES(Dates[Date]),
OrgTMinPrice)
VAR Mindates = CALCULATE(MIN(Dates[Date]),ALLSELECTED())
VAR Maxdates = CALCULATE(MAX(Dates[Date]),ALLSELECTED())
VAR Result = CALCULATE(AveragePrice,DATESBETWEEN(Dates[Date],Mindates,Maxdates))

Return
Result

I hope it makes sense what I am asking.

Thanks,

Patrick N

2

HI @ambepat

Could you please help to share the sample pbix file so replicate the same.

If you have sensitive data then you can use below method to mask your data.

Thanks
Mukesh

1 Like
3

Hi @MK3010 here is what I mean visually,

image
image376×501 17.7 KB

I want to calculate the average price but I want it to only respond to the slicer dates and not the date in the column. Does that make it a bit clearer? I don’t know if we will need a disconnected table here.

Patrick

4

HI @ambepat

If I understand correctly then you are asking to show data for only Min date and Max date from the date slicer, if you can share your pbix file then it will be easier for forum expert to help you. I am adding below video which might help in using disconnected table and using the TRETAS for the filter in disconnected table.

Thanks
Mukesh

5

Hi @ambepat. Your screenshot has not illustrated the desired outcome to me clearly, so perhaps a mock-up of your desired outcome in Excel using the same data as is in your work-in-progress PBIX would help the forum members with their analyses.
Greg

6

@MK3010 Yup he needs to have a disconnected date table.

@ambepat I have made a couple of videos and posts on Disconnected Tables, 1 is already shared by @MK3010 :

7

Hi @Greg @MK3010 @AntrikshSharma here is sample.
Price Analysis.pbix (41.6 KB)

9

HI @ambepat

Not clear what’s your output requirement is but still please see below if it helps.

Averate Price Inch =

VAR MinDate = CALCULATE( MIN( Prices[Date] ) )

VAR MaxDate = CALCULATE( MAX ( Prices[Date] ) )

VAR DiffDate = DATEDIFF( MinDate,MaxDate, DAY )

VAR SUMVal = SUM( Prices[Inch] )

VAR AvValue = DIVIDE( SUMVal, DiffDate )

RETURN

   AvValue

The output is 5.52 average as below

image

Thanks
Mukesh

10

Hi Mukesh,

Yes that value appears in the subtotal but I want it to appear where the dates are. So from the 23.04 onwards down should show 5.52 under the Average Price inch. So
Date Inch Average Price
23-04-2021 4.29 5.52
24-04-2021 4.06 5.52
all the way downwards

Thanks,

Patrick N

11

@ambepat 5.52 for 4/23 - 05/08 ?

If yes, then use the file attached below:
Price Analysis.pbix (58.8 KB)

S =
VAR MinDisDate =
    MIN ( DisDates[Date] )
VAR MaxDisDate =
    MAX ( DisDates[Date] )
VAR CurrentDateInMatrix =
    SELECTEDVALUE ( Dates[Date], MAX ( Dates[Date] ) )
VAR Result =
    IF (
        CurrentDateInMatrix >= MinDisDate
            && CurrentDateInMatrix <= MaxDisDate,
        CALCULATE (
            VAR DatesWithMinInch =
                ADDCOLUMNS ( VALUES ( Dates[Date] ), "@Min Price", [Min Price per Inch] )
            VAR DatesWithoutZero =
                FILTER ( DatesWithMinInch, [@Min Price] <> 0 )
            VAR AverageInch =
                AVERAGEX ( DatesWithoutZero, [@Min Price] )
            RETURN
                AverageInch,
            DATESBETWEEN ( Dates[Date], MinDisDate, MaxDisDate )
        )
    )
RETURN
    Result
12

Hi @ambepat

You can add a measure to get the desire output as below.

VisibleRowAverage = 
CALCULATE ( 
          [Averate Price Inch] , 
          ALLSELECTED()
)

Below screen FYR.

image

Note:- Here the Date Column is from Date Table

Thanks
Mukesh

1 Like
13

Thanks Mukesh. Really appreciate the soluton.

1 Like
14

Thanks @AntrikshSharma . Really appreciate the solution.

15

Hi @ambepat, did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

16

Hi @ambepat, we’ve noticed that no response has been received from you since the 2nd of June. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.