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

jonclay's avatar
jonclay
Helper IV
4 years ago

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.

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    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! 🙂