- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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) 🙂
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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))
))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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))
))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

That makes perfect sense. So I apply my data date as follows:
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
06-21-2024 07:23 AM | |||
06-11-2024 12:36 AM | |||
06-06-2024 03:26 PM | |||
04-29-2024 07:05 PM | |||
08-02-2024 10:40 AM |