Issue with a Measure

[jonclay]

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

[jcalheir]

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]

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

[jcalheir]

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

[jonclay]

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

[jcalheir]

Ok

That will depend on where you are visualizing the Measure, because the filter and row context will be diferent.

Either way, alter your Earliest_Amt to this, so that will remove the Context from all columns except the Contact:

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

[jcalheir]

Ok

That will depend on where you are visualizing the Measure, because the filter and row context will be diferent.

Either way, alter your Earliest_Amt to this, so that will remove the Context from all columns except the Contact:

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

[jonclay]

Thank you.

I'm visualising the data as per below:

I'm basically just dragging the Date measure into the Table and it's automatically putting the earliest Transaction Date against the correct contact.

[jcalheir]

Try This

Earliest_Date =
MINX (
    FILTER (
        ALLEXCEPT ( all_relevant_transactions, all_relevant_transactions[Contact] ),
        all_relevant_transactions[statuscodename] = "Paid"
    ),
    all_relevant_transactions[destcodenet]
)



Earliest_Amt = 
VAR Early_Date = [Earliest_Date]

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

If it still doesn't work, i guess i can only help if you provide a PBIX File or a CSV with some sample data

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

[jcalheir · Accepted solution]

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