locked
How to sum all costs in a month but choose the most recent cost if there is no costs available for that month? RRS feed

  • Question

  • Hi All!

    Below is a subset of my data set

    Project Number    Month              Cost
    8001                    01/01/2015      £50
    8001                    01/02/2015      £70                 
    8001                    01/03/2015      £20
    8002                    01/01/2015      £150
    8002                    01/02/2015      £50
    8002                    01/03/2015      £20
    8003                    01/01/2015      £100
    8003                    01/02/2015      £60

    I want to create a column/measure (I think measure would be the way to do it) so when I produce a pivot table I can add a Timeline. The issue I have is I want to show the most recent cost if the data chosen is not available. For example (using the table above) If I choose 01/03/2015 in the timeline I want the cost to show £20 + £20 +£60 (8001+8002+8003 for the most recent month). If I didnt have the last line in my table it would add £100 on.

    I do have a table of all unique Projects if necessary.

    Cheers!

    Adam



    • Edited by AdamWLavan Thursday, September 24, 2015 2:25 PM
    Thursday, September 24, 2015 2:16 PM

Answers

  • Hi Adam,

    According to your description, you need to sum all the latest cost for each Project in a particular month, right?

    If that is the case, you can use the LASTNONBLANK function to return the latest record based on the select item in timeline. The FIRSTNONBLANK LASTNONBLANK functions return the first and last item, respectively, of the table passed as first argument that returns a non-blank result for the expression passed as second argument. Please refer to the link below to see the details about the functions.
    http://www.sqlbi.com/articles/alternative-use-of-firstnonblank-and-lastnonblank/

    Regards,


    Charlie Liao
    TechNet Community Support

    Friday, September 25, 2015 4:08 AM

All replies

  • Hi Adam,

    According to your description, you need to sum all the latest cost for each Project in a particular month, right?

    If that is the case, you can use the LASTNONBLANK function to return the latest record based on the select item in timeline. The FIRSTNONBLANK LASTNONBLANK functions return the first and last item, respectively, of the table passed as first argument that returns a non-blank result for the expression passed as second argument. Please refer to the link below to see the details about the functions.
    http://www.sqlbi.com/articles/alternative-use-of-firstnonblank-and-lastnonblank/

    Regards,


    Charlie Liao
    TechNet Community Support

    Friday, September 25, 2015 4:08 AM
  • Thanks for this.

    I had a go with this method and for 01/03/2015 it shows £30 (=£20+£10) when it should show £90 (=£20+£10+£60).

    Will this method work if there are no blanks in my data. For example (using my data above) The record for 8003 01/03/2015 doesn't exist, not that it does exist but the cost is blank?

    Cheers!

    Adam

    Friday, September 25, 2015 7:40 AM