DAX Nested IF issue

[Joel_Truuvert]

Hi,

Having trouble with nested IFs (or if there is another solution that is welcome as well) 

The aim is to have the outstanding accounts receivable summed by the last day of each month.

The last day of the month is a separate table of dates selected in a slicer. 

The data set is organized by order numbers with an entry having between 1-5 rows.
The data set has 3 columns with dates I require the slicer value to be checked for.

1. Delivery date of goods
2. Invoice issue date
3. Payment date from the client

IF delivery date <= slicer selected date
IF invoice issue date <= slicer selected date
IF client payment date > slicer selected date
 
IF all of these are true then the amount should be summed up to the outstanding, if any are not true then do not summarize / count as 0 for that row.

e.g.

Order#	Delivery Date	Invoice Date	SUM	Payment date
1	16-Jan	05-Feb	100.00	20-Jan
2	20-Jan	22-Jan	150.00	09-Feb
2	20-Jan	22-Jan	10.00	09-Feb
2	20-Jan	22-Jan	20.00	09-Feb
3	02-Feb	10-Feb	35.00	15-Feb
4	28-Jan	31-Jan	40.00	07-Feb
4	28-Jan	31-Jan	200.00	07-Feb
5	01-Jan	02-Feb	1,000.00	03-Mar
5	01-Jan	02-Feb	5.00	03-Mar

So if the slicer would have 31 Jan 2020 selected it should summarize order 2 and order 4 for the total of 420.00 

Seems like a simple thing but cannot wrap my head around DAX with this, your help is much appreciated.

Kind Regards
Joel Truuvert

[Anonymous]

Hi @Joel_Truuvert ,

You can try out below piece of code:

SUM_Value = 

// Get slicer selected date (if more than 1 or none selected take latest available

var vSelectedDate = SelectedValue(SlicerDateTable[Date],Max(SlicerDateTable[Date]))


Return // Calculate the thing

Calculate(
Sum(Table[SUM]) //Calculate the total 
,
Table[Delivery Date] <= vSelectedDate // IF delivery date <= slicer selected date
,
Table[Invoice Date] <= vSelectedDate // IF invoice issue date <= slicer selected date
,
Table[Payment date] > vSelectedDate  //IF client payment date > slicer selected date
)
+ 0 //if any are not true then do not summarize / count as 0 for that row.

[Anonymous · Accepted solution]

Try this

measure=if(selectevalue(table [delivery date]) <= min(date[date]) && selectevalue(table [invoice issue date]) <= min(date[date]) && selectevalue(table [client payment date]) > min(date[date]), sum(table[amount column]),0)

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

check my blog here
https://community.powerbi.com/t5/Community-Blog/Connecting-to-a-Tabular-Model-Using-Power-BI/ba-p/91...

[Joel_Truuvert]

Tweaked around and got it to work in the intended way using the matrix. One issue though is that- how could I get the measure to sum at the end of the matrix table, would that requier another measure?

Also while each outstanding amount is order# based, it currently doesn't aggregate the amounts by selected clients, I originally did not include this as I though that PB will aggregate all the orders amounts under clients by default.

So essentially I would like to have the total of outstanding per client and TOTAl at the end of each day BY order based aggregation if that is posslble.

Thank You & Kind Regards
Joel Truuvert