Forum Discussion

Kamill11's avatar
Kamill11
Icon for Helper I rankHelper I
4 years ago
Solved

One last date value from 3 measures.

Hi everyone,

I have three measures that display the last document by date. Now, out of these three measures, I want to make another one that will show the latest documents from these three measures. Can you help me? I made such a measure, but it does not work properly, you can see in the picture 🙂

 

 

Last DOC = 

VAR __list = { 'Second Querry'[Last GRN Doc No_], 'Third querry'[Last PIN Doc No_], 'First Querry'[Last BOM Doc NO_] }

RETURN 

 LASTNONBLANK( __list, [Value] )

 

 

Need Help
Show and Tell
Tips and Tricks
  • lbendlin's avatar
    lbendlin
    4 years ago

    Did you know that the MAX() function can take two parameters?

     

    Anyway. You can UNION() tables even if they have different column names, as long as the column types are the same and the number of columns is the same.  The first table will define the column names for the result, much like in SQL.

    Here is the pseudo code:

     

    Last Doc = 
var c = UNION(GRN,PIN,POR)
var l = TOPN(1,c,[Date],DESC)
return CONCATENATEX(l,[GRN])

     

     You can run this straight against your source tables, no need for any intermediate measures.

     

    See attached for an example.

5 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    you are not indicating how you compute the three measures. Since you need the date value across all three it might be better to create a new measure that recreates the other three via UNION.

    • Kamill11's avatar
      Kamill11
      Icon for Helper I rankHelper I
      4 years ago

      lbendlin 

      Data and measure looks like that :

       

      Last DOC TEST = 


VAR __list = { CALCULATE(LASTNONBLANK('GRN'[Document No_],"" ), LASTDATE('GRN'[Posting Date])),
                CALCULATE(LASTNONBLANK('PIN'[Document No_],""), LASTDATE('PIN'[Posting Date])),
                CALCULATE(LASTNONBLANK('POR'[Document No_],""), LASTDATE('POR'[Finished Date])) }

VAR __date = { LASTDATE('GRN'[Posting Date]), LASTDATE('PIN'[Posting Date]), LASTDATE('POR'[Finished Date])}


RETURN 

CALCULATE(LASTNONBLANK(__list , [Value]), TREATAS(__date, 'Item Table'[No_])) 


//Item table is table with name of all items in database

       



      I wanna show LAST generated document, in this example it should be PIN-031346 🙂 

       

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        4 years ago

        Did you know that the MAX() function can take two parameters?

         

        Anyway. You can UNION() tables even if they have different column names, as long as the column types are the same and the number of columns is the same.  The first table will define the column names for the result, much like in SQL.

        Here is the pseudo code:

         

        Last Doc = 
var c = UNION(GRN,PIN,POR)
var l = TOPN(1,c,[Date],DESC)
return CONCATENATEX(l,[GRN])

         

         You can run this straight against your source tables, no need for any intermediate measures.

         

        See attached for an example.