Forum Discussion

PietroFarias's avatar
PietroFarias
Resolver II
9 years ago
Solved

Return LAST DATE from table

Hi! I'm trying calculate the percentual change of a value by the value of the last date. In my table, there is a columns with the update date. I used LASTDATE, LASTNONBLANK and other, but i return only same day of update date and not the last update date. 

 

I hope you understand the example below. I've a Sales Table where and is updated whenever there is an update in the database. For example:

And i've other Table with the Periods:

 

I want to see the Percentage change from one date to another. How can i do this with DAX?

  • PietroFarias's avatar
    PietroFarias
    9 years ago

    Thanks Charlie!
    How i'm a noob with DAX, i went to study about this functions. So, i came to an adaptation to know the amount of sales made.

     

    First, i retrieved the last date in a column:

     

    LstDate = MAXX(
               FILTER(
                      Sales;
                      Sales[Date] < EARLIER(Sales[Date])
               );Sales[Date]
          )

     

    Second, i retrieved the number of rows in a filter:

    LstSale = COUNTROWS(
                        FILTER(
                               Sales;
                                  Sales[Name] = EARLIER(Sales[Name]) && 
                                  Sales[Date]=EARLIER(sales[LstDate])
                       )
               )

     

    And finally, i wrote a measure:

     

    upSales = 
    VAR vlSales = SUM(Sales[LstSale])
    RETURN
     IF(
        vlSales=BLANK();
        BLANK(); 
        DIVIDE(
               [ActSales]; //Existing measure
               vlSales)-1
     )

     

     if i want to know the change percentage from last sale, a create a column with the last sale, similar to last example.

2 Replies

  • v-caliao-msft's avatar
    v-caliao-msft
    Microsoft Employee
    9 years ago

    PietroFarias,

     

    Create two calculated to achieve your requirement.

    Rnak = RANKX(FILTER(Sale,Sale[Name]=EARLIER(Sale[Name])),Sale[Date],,ASC)
    PercentageChange = (Sale[Sales]-LOOKUPVALUE(Sale[Sales],Sale[Name],Sale[Name],Sale[Rnak],Sale[Rnak]-1))/LOOKUPVALUE(Sale[Sales],Sale[Name],Sale[Name],Sale[Rnak],Sale[Rnak]-1)

     

    Regards,

    Charlie Liao

    • PietroFarias's avatar
      PietroFarias
      Resolver II
      9 years ago

      Thanks Charlie!
      How i'm a noob with DAX, i went to study about this functions. So, i came to an adaptation to know the amount of sales made.

       

      First, i retrieved the last date in a column:

       

      LstDate = MAXX(
               FILTER(
                      Sales;
                      Sales[Date] < EARLIER(Sales[Date])
               );Sales[Date]
          )

       

      Second, i retrieved the number of rows in a filter:

      LstSale = COUNTROWS(
                          FILTER(
                                 Sales;
                                    Sales[Name] = EARLIER(Sales[Name]) && 
                                    Sales[Date]=EARLIER(sales[LstDate])
                         )
                 )

       

      And finally, i wrote a measure:

       

      upSales = 
      VAR vlSales = SUM(Sales[LstSale])
      RETURN
       IF(
          vlSales=BLANK();
          BLANK(); 
          DIVIDE(
                 [ActSales]; //Existing measure
                 vlSales)-1
       )

       

       if i want to know the change percentage from last sale, a create a column with the last sale, similar to last example.