locked
Sum latest - 3 conditions RRS feed

  • Question

  • Dear forum, 

    I am tracking how projects evolves, so I am gathering templates that always gets updated, in a single folder.

    For ex: 

    SiteA ProjectA 100113 ; SiteA ProjectA 011214 (mmddyy style)

    SiteE ProjectB 010115 ; SiteE ProjectB 011215

    SiteF ProjectB 020115

    SiteF ProjectA 020115

    I am trying to present in my charts only the sum of the latest file in each project & site (as it contains the latest data).

    I want to display it in a pivot table and chart, like:

    Year

           Project

                     Material Group

                                             Item

    Any thoughts? 

    I assume it is done with Calculate & filters, but no idea how :)


    עמית


    • Edited by עמיתת Wednesday, July 29, 2015 7:33 AM
    Wednesday, July 29, 2015 7:27 AM

Answers

  • Hi,

    According to your description, you need to sum up the latest file in each project, right?

    In your sample data, there is a date column, so we can use lastdate function to get the last file. We can use LASTDATE to get the last day active in the current filter context for a particular date column passed as an argument.
    The sample DAX expression below is for you reference.
    =CALCULATE ( SUM ( Inventory[UnitsBalance] ), LASTDATE ( 'Date'[Date] ) )
    Please refer to the link below to see the details about LASTDATE function in DAX.
    http://www.sqlbi.com/articles/semi-additive-measures-in-dax/

    Regards,


    Charlie Liao
    TechNet Community Support

    Thursday, July 30, 2015 9:47 AM

All replies

  • Can you supply some better sample data, an example of one of the files?

    On the import front, you will probably have to keep the latest files in a separate directory and use the Folder import functionality of Power Query to grab all of them.  You can do this if they are all in the same format. Otherwise, you could use individual queries and an Append query to mash them all together.

    Wednesday, July 29, 2015 11:59 AM
  • Thank you.

    You are right and I already did the power query correctly. Everything was uploaded to PP correctly. 

    I can't supply the data as it is conf', but this is roughly the formation of the files:

    File Date Site Project Item# Spend

    12/12/14   IS     P1111 AUY209 $50

    12/12/14   IS     P1111 AUI200 $450

    12/01/15   IS     P1111 AUY209 $150

    12/01/15   IS     P1111 AUY235 $100

    12/06/15   IR     P2222 AUY50 $10

    12/06/15   IR     P2222 AUI1 $5

    There are 2 projects in this example: IS-P1111, IR-P2222.

    IS-P1111 last throughout 2014 & 2015, so the last data is the most accurate one, saying that in the pivot table I won't see IS-P1111 in 2014, but only at 2015 and have 2 item with total spend of $250.

    In addition, IR-P2222 will also be displayed at year 2015 with 2 items and total spend of $15.

    Here is how I vision my pivot to look like:

    2015                                      $265

             IS-P1111                     $250

                             AUY209          $150

                             AUY235          $100

             IR-P2222                     $15

                              AUY50           $10

                              AUI1             $5

    *I might be asked to also show 2014 latest data (so that IS-P1111 will be shown also under 2014 with its data till end of year 2014- $500), so if you know how to accomplish that also- that will be great!

    Thanks much!



    עמית

    Wednesday, July 29, 2015 12:34 PM
  • Thank you.

    You are right and I already did the power query correctly. Everything was uploaded to PP correctly. 

    I can't supply the data as it is conf', but this is roughly the formation of the files:

    There are 2 projects in this example: IS-P1111, IR-P2222.

    IS-P1111 last throughout 2014 & 2015, so the last data is the most accurate one, saying that in the pivot table I won't see IS-P1111 in 2014, but only at 2015 and have 2 item with total spend of $250.

    In addition, IR-P2222 will also be displayed at year 2015 with 2 items and total spend of $15.

    Here is how I vision my pivot to look like:

    2015                                      $265

             IS-P1111                     $250

                             AUY209          $150

                             AUY235          $100

             IR-P2222                     $15

                              AUY50           $10

                              AUI1             $5

    *I might be asked to also show 2014 latest data (so that IS-P1111 will be shown also under 2014 with its data till end of year 2014- $500), so if you know how to accomplish that also- that will be great!

    Thanks much!



    עמית



    עמית

    Wednesday, July 29, 2015 12:41 PM
  • Thank you.

    You are right and I already did the power query correctly. Everything was uploaded to PP correctly. 

    I can't supply the data as it is conf', but this is roughly the formation of the files:

    There are 2 projects in this example: IS-P1111, IR-P2222.

    IS-P1111 last throughout 2014 & 2015, so the last data is the most accurate one, saying that in the pivot table I won't see IS-P1111 in 2014, but only at 2015 and have 2 item with total spend of $250.

    In addition, IR-P2222 will also be displayed at year 2015 with 2 items and total spend of $15.

    Here is how I vision my pivot to look like:

    2015                                      $265

             IS-P1111                     $250

                             AUY209          $150

                             AUY235          $100

             IR-P2222                     $15

                              AUY50           $10

                              AUI1             $5

    *I might be asked to also show 2014 latest data (so that IS-P1111 will be shown also under 2014 with its data till end of year 2014- $500), so if you know how to accomplish that also- that will be great!

    Thanks much!



    עמית



    עמית

    Of course, if I have items of IS-P1111 before 12-01-15, they won't be included in the pivot table, since they are not the latest :)

    עמית

    Wednesday, July 29, 2015 12:53 PM
  • Hi,

    According to your description, you need to sum up the latest file in each project, right?

    In your sample data, there is a date column, so we can use lastdate function to get the last file. We can use LASTDATE to get the last day active in the current filter context for a particular date column passed as an argument.
    The sample DAX expression below is for you reference.
    =CALCULATE ( SUM ( Inventory[UnitsBalance] ), LASTDATE ( 'Date'[Date] ) )
    Please refer to the link below to see the details about LASTDATE function in DAX.
    http://www.sqlbi.com/articles/semi-additive-measures-in-dax/

    Regards,


    Charlie Liao
    TechNet Community Support

    Thursday, July 30, 2015 9:47 AM