Combined conditional measure that depends on current date

[Cadenze]

I have a pickle that I'm struggling with and I need someone really competent in DAX to help me. I need one measure that is one thing if this month is selected, and another if we go back in time. It has to be one measure to allow the use of the same visual with a month selector.

So open amount is

=CALCULATE( SUM(OrderBook[Open amount]),LASTDATE('Date'[Date]))

if we select the current month

and

=CALCULATE( SUM(OrderBook[Open Amount]),LASTNONBLANK('Date'[Date],[OpenQtyNow]))

if we select a previous month.

I have tested the two separately and they do what I want, but I need them in one measure and I am not a DAX wizard (yet) 🙂

[Cadenze · Accepted solution]

I ended up with this construct that yielded the required result (in case anyone with the same problem visits this post):

OpenamtNow =

CALCULATE( SUM(OrderBook[Open amount]),LASTNONBLANK('Date'[Date],

CALCULATE(SUM(OrderBook[Open amount]), FILTER( ALL('Customers'),1))

))

[Cadenze]

[Cadenze]

That looks good - however, it leaves the open amount for this month totally empty. Maybe I dreamt something up that is not doable. Let me ponder - I will supply a pbix for a more detailed problem description. Thank you anyway 🙂

[SamWiseOwl]

Is your Date last date the same as your data last date?

For example my last sale could be 26/09/2024 but in my calendar table it would be 31/12/2024

If not that would explain the blank.

Swap measure =
If(
month(today()) = month(lastdate(OrderBook[OrderDate]))

,CALCULATE( SUM(OrderBook[Open amount]),lastdate(OrderBook[OrderDate]))
,CALCULATE( SUM(OrderBook[Open Amount]),LASTNONBLANK('Date'[Date],[OpenQtyNow]))
)

[Cadenze]

That makes perfect sense. So I apply my data date as follows:

OpenAmtNow = If(

month(today()) = month(lastdate('Orderbook'[Transdate]))

,CALCULATE( SUM(OrderBook[Open amount]),LASTDATE('OrderBook'[Transdate]))

,CALCULATE( SUM(OrderBook[Open Amount]),LASTDATE('Date'[Date]))

)

and leave out LASTNONBLANK as it is not appropriate for what I am trying to achieve. So for all previous months, I would like the OpenAmt that was greater than zero at the last day of the month. This I pick from the date table.

For the current month, I still do not have an end-of-month amount, and so LASTDATE may not be the correct measure. The alteration:

OpenAmtNow = If(

month(today()) = month(lastdate('Orderbook'[Transdate]))

,CALCULATE( SUM(OrderBook[Open amount]),LASTDATE('OrderBook'[Transdate]))

,CALCULATE( SUM(OrderBook[Open Amount]),LASTDATE('Date'[Date]))

)
should then give me the open amount for today (last record in the Orderbook table), but it gives me the open amount for the last known nonblank amount. 
So an account had an open amount three days ago, but we shipped and invoiced him, so he now has nothing open. But it still shows as if he does. 

[SamWiseOwl]

Hi @Cadenze 

Not sure I 100% understand what you are asking but I will have a go!

Swap measure =
If(
month(today()) = month(lastdate(date[date]))

,CALCULATE( SUM(OrderBook[Open amount]),LASTDATE('Date'[Date]))
,CALCULATE( SUM(OrderBook[Open Amount]),LASTNONBLANK('Date'[Date],[OpenQtyNow]))
)