Help in building Calculate Table using multiple conditions

[apatwal]

Hi,

I was trying to build calculated table. Requirement is as below:

I have below master data from which I have to create calculated table.

In my calculate table I have to take all those records from master table, when the below two conditions are met:

1. Profit % 2021<Profit % 2020

2. If the above condition is true, then check if there are any previous transaction of same customer and product, regardless of Profit% consider all of them and insert them to calculated table.

So, the transaction that happened on 07/26/2021 matches the condition Profit % 2021 < Profit % 2020; so along with this transaction all previous transaction (01/19, 02/09, 02/25, 04/21, 06/16) should be considered in my calculated table.

The last transaction that happened on 11/24/2021 should not be considered in my end table as Profit % 2021 > Profit % 2020

I have applied below DAX to meet the condition 1 for calculated table

Table = CALCULATETABLE(master_table,

FILTER(master_table,[Profit % 2021] < [Profit % 2020]))

this results in below table. 

But in my report to need below results I need to apply 2nd condition as well in my calculated table

How can we achieve this using DAX or any other method within Power BI?

[tamerj1]

Hi @apatwal 
Not sure if I fully understand your query. However, you can try creating a calculated column in master data table as follows:

Keep? = 
VAR CurrentDate = 'Master data'[Invoice Date]
VAR CurrentCustomer = 'Master data'[Customer Name]
VAR CurrentProduct = 'Master data'[Product]
VAR FilteredTable =
    CALCULATETABLE (
        'Master data',
        'Master data'[Customer Name] = CurrentCustomer,
        'Master data'[Product] = CurrentProduct,
        'Master data'[Profit % 2021] < 'Master data'[Profit % 2020],
        REMOVEFILTERS()
    )
VAR LastInvDate = 
    MAXX (
        FilteredTable,
        'Master data'[Invoice Date]
    )
RETURN
    IF (
        CurrentDate <= LastInvDate,
        TRUE
    )

which basically retrieves the last invoice date where all the conditions are true and then compare it with the current date as it should be always less.
you can use this colum to calculate a new table where this column is TRUE.
Hope this solves your issue
Here's the file https://www.dropbox.com/t/v6CObFE1SgdTFJV9

[apatwal]

@lbendlin 

Could you please elaborate.

I even tried to use below DAX

Table =

var startdate =

MINX(

FILTER(

ALLSELECTED(master_table),

master_table[Customer Name]=MAX(master_table[Customer Name]) &&

master_table[Product] = MAX(master_table[Product])

),

master_table[Invoice Date])

var end_date =

MAXX(

FILTER(

ALLSELECTED(master_table),

master_table[Customer Name]=MAX(master_table[Customer Name]) &&

master_table[Product] = MAX(master_table[Product]) &&

[Profit % 2021]<[Profit % 2020]

),

master_table[Invoice Date])

RETURN CALCULATETABLE(

master_table,

DATESBETWEEN(master_table[Invoice Date],startdate,end_date)

)

Using the above DAX, I am getting the previous transaction for Customer and Product, but its giving me the future transation as well which we don't want in our report.

Any help or guidance is appreciated.. 

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. 

[apatwal]

Hi @lbendlin 

Please find the attached power bi file

https://drive.google.com/file/d/1csERaKmt56_RwBB5XSa1mxU0YLlW3jqN/view?usp=sharing

Let me know if it helps you.

[lbendlin · Accepted solution]

You don't need to create a new calculated table. You can apply a filter to your existing Master Data table. But you also need to account for the scenario where Profit % 2021 was NOT smaller that Profit % 2020 for any of the rows.

Include = 
var ml = CALCULATE(max('Master data'[Invoice Date]),ALLEXCEPT('Master data','Master data'[Customer Name]),'Master data'[Profit % 2021]<'Master data'[Profit % 2020])
return SWITCH(TRUE(),ISBLANK(ml),0,'Master data'[Invoice Date]<=ml,1,0)

See attached.

[lbendlin]

use ALLEXCEPT to identify all customers that satisfy the requirement. Then use that list of customers to list all their transactions.