DAX - ALLSELECTED with Additional Column in Table

[lrochester]

Hi

I have created a calculated column to give me the previous Sale Date and a calculated measure to calculate the avg TxnAmt.

I have several report slicers so have used ALLSELECTED but I also need to take a table column value (Supplier) into consideration.

Here's my DAX so far...

PrevSaleDate =
VAR CurrentSaleDate = 'Data'[SaleDate]
RETURN
CALCULATE(MAX('Data'[SaleDate]),
FILTER(ALLSELECTED('Data'),
'Data'[SaleDate] < CurrentSaleDate
)
)

PrevSaleDateAvgTxnAmt = CALCULATE(
AVERAGE( 'Data'[TxnAmt] ),
FILTER ( ALLSELECTED ( 'Data' ), MAX( 'Data'[PrevSaleDate] ) = 'Data'[SaleDate] )
)

Attached is a pbix with example data and the calculated column and measure - Example.pbix

I'm stuck at the last hurdle so any help would be greatly appreciated!

Thanks

[Zubair_Muhammad]

Hi @lrochester

Check this one

PrevSaleDate =
VAR CurrentSaleDate = 'Data'[SaleDate]
RETURN
    CALCULATE (
        MAX ( 'Data'[SaleDate] ),
        FILTER (
            ALLEXCEPT ( 'Data', Data[Supplier] ),
            'Data'[SaleDate] < CurrentSaleDate
        )
    )

[lrochester]

Thanks, Zubair.

Neither are quite right though I'm afraid.

I've add some more example data, applied your suggestions, and shown the expected results in this pbix.

[lrochester]

Forgot to add...

In the actual report pbix, there will be quite a few more slicers than in the example (11 in total) so I originally opted for the ALLSELECTED function.

Also, the row count of the data is going to be in the 100's of millions so I need the calculated column and measure to be super slick.

Thanks again and I really appreciate your help 🙂

[v-shex-msft]

Hi @lrochester.

>>Also, the row count of the data is going to be in the 100's of millions so I need the calculated column and measure to be super slick.

Allselceted suitbale for your requirement, you need to double check on relationships to confirm you have modified 'cross fitler direction' option to 'both'. Otherwise the filter effect only works on one side.

Regards,

Xiaoxin Sheng

[lrochester]

Hi Xiaoxin

All of the columns I need are in one table so there's no need to join in this instance 🙂

Would you be able to send me the DAX I should use for PrevSaleDate and PrevSaleDateAvgTxnAmt please? 

Thanks

Lucy

[v-shex-msft]

Hi @lrochester,

Perhaps you can try to use below measure if it suitable for your requirement:

PrevSaleDateAvgTxnAmt =
VAR previousDate =
    MAXX (
        FILTER ( ALLSELECTED ( 'Data' ), 'Data'[SaleDate] < MAX ( 'Data'[SaleDate] ) ),
        [SaleDate]
    )
RETURN
    AVERAGEX (
        FILTER ( ALLSELECTED ( 'Data' ), previousDate = 'Data'[SaleDate] ),
        [TxnAmt]
    )

Regards,

Xiaoxin Sheng

[Zubair_Muhammad]

Hi @lrochester

PrevSaleDateAvgTxnAmt =
CALCULATE (
    AVERAGE ( 'Data'[TxnAmt] ),
    FILTER (
        ALLEXCEPT ( Data, Data[Supplier], Data[SaleType], Data[DiscountApplied] ),
        'Data'[SaleDate] = SELECTEDVALUE ( Data[PrevSaleDate] )
    )
)