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 th...
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! 🙂

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?

      • jonclay's avatar
        jonclay
        Helper IV
        4 years ago

        Hi there

        Thank you for your reply. Hopefully the images below will show you what is happening:

        The Date Measure is working correctly as it's pulling out 26/07/2007 as the earliest date for this contact:


        However, the Amount Measure is still pulling out £100 as it is the lowest amount. I completely understand Jose's reasons in the comments above, but I'm still getting £100 when I change the Measure.

        Could it be because I'm not referencing the SerialID field (i.e. the unique contact ID) anywhere in the Measures?

        Many thanks
        Jon