Forum Discussion

Cadenze's avatar
Cadenze
Icon for Helper II rankHelper II
2 years ago
Solved

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 h...
Need Help
  • Cadenze's avatar
    Cadenze
    2 years ago

    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's avatar
Cadenze
Icon for Helper II rankHelper II
2 years ago

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's avatar
    SamWiseOwl
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      Cadenze
      Icon for Helper II rankHelper II
      2 years ago

      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.