DAX: filtering with date placeholders

[Anonymous]

Hello there,

I have the following situation: 

I have two tables:

• transactions
• users

TRANSACTIONS

id	userId	type	createdAt	processedAmount

USERS

id	name	[Calculated] Deposits	[Calculated] Withdrawals	[Calculated] Net Deposit

Deposits = 
    CALCULATE(
        SUM('transactions'[processedAmount]), 
        FILTER (
            ALL('transactions'),
            'transactions'[userId] = 'users'[id]
            && ('transactions'[type] = "deposit")
        ) 
    )

Withdrawals = 
    CALCULATE(
        SUM('transactions'[processedAmount]), 
        FILTER (
            ALL('transactions'),
            'transactions'[userId] = 'users'[id]
            && ('transactions'[type] = "withdrawal")
        ) 
    )

Net Deposit = [Deposits] - [Withdrawals]

I want to create a date filter with the createdAt value. However, I missed this binding when created calculated columns. Is it any way to use the FILTER in Dax with date placeholders so the slicer will affect such columns.

Thanks in advance.

[amitchandak]

@Anonymous , Not very clear. You can join created at (Without time stamp), with a date table, and use that in the filter ?

[Anonymous]

I don't think so. 

Actually, I have two calculated columns: Deposits and Withdrawals. 
I created the slicer contained the createdAt values from the TRANSACTION table. 

For example,

I specify the date range: 1/1/20 - 1/1/21.

I need to get the Sum of all processedAmounts with type = Deposit which was created in the specified date range (the same for Withdrawals), but I'm getting all deposits.

[dax]

Hi @Anonymous ,

I am not clear about your requirement, if possible could you please inform me more detailed information(such as your expected output and your sample data )? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Zoe,

Thanks for your reply! Here you can find the file with sample data: https://drive.google.com/file/d/1w__Dy-dWfSB9KQomgWHqKPpnrAk2vn30/view?usp=sharing

On the dashboard, you can see one slicer and two tables. Slicer works only for one table, I need for both.

[dax · Accepted solution]

Hi @Anonymous , 

You could use measure to achieve this goal. You could refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.