Forum Discussion

jonclay's avatar
jonclay
Helper IV
4 years ago
Solved

Issue with a Measure

Hi everyone

I've written the following measure that pulls back the latest date of a sale from a list of sales. I then place this in my Power BI report against a contact, so it effectively shows the first ever sale date by that contact:

Earliest_Date = MINX(FILTER(all_relevant_transactions,all_relevant_transactions[statuscodename]="Paid"), all_relevant_transactions[transactiondateofpayment])

I've then created the following measure to pull out the amount of the first sale:

Earliest_Amt = MINX(FILTER(all_relevant_transactions,all_relevant_transactions[statuscodename]="Paid"), all_relevant_transactions[destcodenet])

This is where I have the problem. Instead of pulling the Amount relating to the earliest sale, it's simply pulling the lowest Amount. So, in the example below I'd like it to bring back the amount of £250.00, but the Measure is bringing back £50.00 as it's the lowest amount.

How can I amend the above Measure to bring back the Amount relating to the Earliest dated Sale?


Many thanks for your help
Jon

 
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi

     

    You needed to define the Earlyest date in the variable, so it is defined in the Scope's Row context.

     

    Bellow the two formulas

     

     

    Earliest_Date = 
MINX (
    FILTER (
        all_relevant_transactions,
        all_relevant_transactions[statuscodename] = "Paid"
    ),
    all_relevant_transactions[si_transactiondateofpayment]
)

     

     

     

    Earliest_Amt = 
VAR Early_Date = [Earliest_Date]

RETURN
CALCULATE(
    MIN(all_relevant_transactions[destcodenet]),
    FILTER(
        all_relevant_transactions,
        all_relevant_transactions[si_transactiondateofpayment] = Early_Date
    )
)

     

    Kind regards,
    José
    Please mark this answer as the solution if it resolves your issue.
    Appreciate your kudos! 🙂

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi

     

    That is a normal behaviour, as you are querying the minimum value of the  all_relevant_transactions[destcodenet].

     

    What you need to do is to get the value from the earlyest date. Try something like this:

     

    Earliest_Date = MINX(FILTER(all_relevant_transactions,all_relevant_transactions[statuscodename]="Paid"), all_relevant_transactions[transactiondateofpayment])


Earliest_Amt = 
CALCULATE(
    MIN(all_relevant_transactions[transactiondateofpayment]),
    FILTER(
        all_relevant_transactions,
        all_relevant_transactions[transactiondateofpayment] = [Earliest_Date]
    )
)

     

    Kind regards,
    José
    Please mark this answer as the solution if it resolves your issue.
    Appreciate your kudos! 🙂

    • jonclay's avatar
      jonclay
      Helper IV
      4 years ago

      Hi José

      Many thanks for your reply.

      I've tried making these changes to my Measures but I'm now getting a date on the Earliest_Amt Measure instead of a numerical value. I've tried changing the Measure to the one below, but then I just get the same as I did before.

       

      CALCULATE
          ( MIN(all_relevant_transactions[destcodenet]),
          FILTER(
              all_relevant_transactions,
              all_relevant_transactions[transactiondateofpayment] = [Earliest_Date]
              )
          )

      Do you know what I'm doing wrong?

      Many thanks
      Jon
      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Could you provide a sample from your data? Or maybe a PBIX file?