DAX - compare date field with another date field

[fab]

Hello,

I have a date table with the current date and another date field to compare data with. This compare date is not deductible from the current date, it is hard written in the source file.
Example:
current date - compare date
1/1/2017 - 5/1/2017
2/1/2017 - 3/1/2017
3/1/2017 - 6/1/2017
4/1/2017 - 8/1/2017
5/1/2017 - 4/1/2017

Here is a second table with the amout:
current date - amout
1/1/2017 - 10
2/1/2017 - 20
3/1/2017 - 7
4/1/2017 - 13
5/1/2017 - 15
6/1/2017 - 26
7/1/2017 - 8
8/1/2017 - 35


I would like to have a visual table like that:
current date - amount current date - amont compare date
1/1/2017 - 10 - 15
2/1/2017 - 20 - 7
3/1/2017 - 7 - 26
4/1/2017 - 13 - 35
5/1/2017 - 15 - 13

How can I do that?
How can I filter the amount with another field date and display it in a table?

[v-piga-msft]

Hi @fab,

You could create a calculated table with the formula below.

Table =
SUMMARIZE (
    'Table1',
    Table1[Current date],
    'Table1'[Compare date],
    "amount current date ", LOOKUPVALUE ( 'Table2'[Amount], 'Table2'[Current Date], 'Table1'[Current date] ),
    "amont compare date", LOOKUPVALUE ( 'Table2'[Amount], Table2[Current Date], 'Table1'[Compare date] )
)

Here is you desired output.

More details you could have a reference of the attachment.

Hope this can help you!

Best  Regards,

Cherry

[fab]

Hello,

Thank you for your answer,

It works for the example I gave.

Actually, it's a small part of my report. My model contains many tables organized in star schema.

I am not sur this method is appropriate in my case (lookup value with many column, filters from others tables...)

Is it still working if I add others tables to my example, let's say, table 3 for product table and table 4 for customer (and so several possible values for the same date)?

regards

fab

[v-piga-msft]

Hi @fab,

It should work, you could have a try.

If you need further help, please share more details about your scenario, so that we can help further investigate on it?

If my reply help you, only thing that you'll have to notice, just always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

Best Regards,

Cherry

[fab]

@v-piga-msft

 Thank you for helping me,

Here are my questions (I changed tables names and add the product table):

The new "table" is no longer works:

then, I don't know how to link this table with the others tables to apply filters (for exemple, a product "color" filter)

(I don't know how to add an attachment to this post)

regards,

Fab

[v-piga-msft]

Hi  @fab,

You can upload your dummy pbix file to OneDrive or Dropbox and post the link here. (Do mask sensitive data before uploading.)

Best Regars,

Cherry

[fab]

here is the report exemple I used: report

[v-piga-msft]

Hi @fab,

From your data model, the error is caused the duplicate values in current date column. Since the LOOKUPVALUE function is only expecting a single column with one unique or distinct value.

Now, your data model is differently from the first post, could you show me your desired output?

Best Regards,

Cherry

[fab]

Hello,

I would like the same table as my first question:

current date - amount current date - amont compare date
1/1/2017 - 210 - 15
2/1/2017 - 225 - 12
3/1/2017 - 12 - 26
4/1/2017 - 29 - 35
5/1/2017 - 15 - 29

6/1/2017 - 26 - 29

7/1/2017 - 8

8/1/2017 - 35

I would like this table dynamically, if I want to add the color or the product, the good agregate amount must be displayed.

The real model contains several other tables....

fab

[fab · Accepted solution]

I found the solution: