Forum Discussion

Atinder's avatar
Atinder
Helper III
3 years ago
Solved

Find Last purchase Cost

Hello,    I am need to Find my last invoiced purchased price and last Invoiced date. If there is no invoice leave it blank . I found the last invoice date with measure.  Date of Invoice = LASTDAT...
Data Protection
Dataflow
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , Atinder 

    You can use these dax measures :

    Last Invoice date = LASTDATE('Purchase Rec'[Date Invoiced])
    Cost = CALCULATE( SUM( 'Purchase Rec'[COST]) , LASTDATE('Purchase Rec'[Date Invoiced]))

    (2)Then you can put them on the visual , the result is as follows:

     

    If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , Atinder 

    You can update the measure to this:

    Last Invoice date = MAX('Purchase Rec'[Date Invoiced])
    Cost = 
var _last_date  = MAX('Purchase Rec'[Date Invoiced])
return
CALCULATE( SUM( 'Purchase Rec'[COST]) , 'Purchase Rec'[Date Invoiced]=_last_date)

    Then we can meet your need , the result is as follows:

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

v-yueyunzh-msft's avatar
v-yueyunzh-msft
Community Support
3 years ago

Hi , Atinder 

The .pbix file share link need a password, i cannot download it , can you share it again so that we can help you better?

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

Atinder's avatar
Atinder
Helper III
3 years ago

Hi @Aniya,

 

Password for the file is 123456.

 

 

Thank you

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Community Support
    3 years ago

    Hi , Atinder 

    You can use these dax measures :

    Last Invoice date = LASTDATE('Purchase Rec'[Date Invoiced])
    Cost = CALCULATE( SUM( 'Purchase Rec'[COST]) , LASTDATE('Purchase Rec'[Date Invoiced]))

    (2)Then you can put them on the visual , the result is as follows:

     

    If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • Atinder's avatar
      Atinder
      Helper III
      3 years ago

      Hi@Aniya,

       

      Thank you it works. 

       

      but I have another question If I have 3 invoices from same date. I want to look up value from the last invoice date and time. How can I lookup that up?

       

      For example: I want to see like this DIS-198  Last invoiced: 1/6/2022   cost:$8.

       

      when I use this formula it gives me Sums up all the cost from 1/6. I want to the most recent one. 

       

      Cost = CALCULATE( SUM( 'Purchase Rec'[COST]) , LASTDATE('Purchase Rec'[Date Invoiced]))

       

      I attached file

      Cost (1).pbix

      Pass: 123456

       

      Thank you!

       

      • v-yueyunzh-msft's avatar
        v-yueyunzh-msft
        Community Support
        3 years ago

        Hi , Atinder 

        You can update the measure to this:

        Last Invoice date = MAX('Purchase Rec'[Date Invoiced])
        Cost = 
var _last_date  = MAX('Purchase Rec'[Date Invoiced])
return
CALCULATE( SUM( 'Purchase Rec'[COST]) , 'Purchase Rec'[Date Invoiced]=_last_date)

        Then we can meet your need , the result is as follows:

         

        Best Regards,

        Aniya Zhang

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly