Reply
Cadenze
Helper II
Helper II
Partially syndicated - Outbound

Combined conditional measure that depends on current date

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) 🙂

1 ACCEPTED SOLUTION
Cadenze
Helper II
Helper II

Syndicated - Outbound

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))

))

View solution in original post

6 REPLIES 6
Cadenze
Helper II
Helper II

Syndicated - Outbound

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
Helper II
Helper II

Syndicated - Outbound
 

OpenAmtNow.png

Cadenze
Helper II
Helper II

Syndicated - Outbound

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 🙂

Syndicated - Outbound

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]))
)

 


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

Syndicated - Outbound

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
Super User
Super User

Syndicated - Outbound

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]))
)


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)