Forum Discussion

giordafrancis's avatar
giordafrancis
Frequent Visitor
5 years ago
Solved

Last month submitted based on id

Consider the below table

 

idmonth submittedbudget
10101/06/202050
10101/07/2020100
10301/06/202050
10301/07/202050
10301/08/202050
10301/08/202020

 

I'm looking to create a measures or a new table

  1. subset the above table based on the last month submitted date for each id ; no aggregation is required.

Desired output is a table as below. To confirm it's not subset by latest overall last month submitted, but last month submitted given the id.

 last month datebudget
10101/07/2020100
10301/08/202020
10301/08/202050

 

 

Thank you in advance

Dataflow
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi giordafrancis 

    You can achieve your goal by build a measure or build calculated tables.

    Measure:

     

    Measure = 
Var _MaxDatePerID = MAXX(FILTER(ALL('Table'),'Table'[id]=MAX('Table'[id])),'Table'[month submitted])
Return
IF(MAX('Table'[month submitted])=_MaxDatePerID,1,0)

     

    Add the measure into table visual's Filter Field and set the measure to show items when the value =1.

    Result is as below.

    Calculated Table:

     

    Subset = CALCULATETABLE('Table',FILTER('Table','Table'[month submitted] = MAXX(FILTER('Table','Table'[id]=EARLIER('Table'[id])),'Table'[month submitted])))
    Subset2 = SUMMARIZE(FILTER('Table','Table'[month submitted]=MAXX(FILTER('Table','Table'[id]=EARLIER('Table'[id])),'Table'[month submitted])),'Table'[id],'Table'[month submitted],'Table'[budget])

     

    Results are the same:

    You can download the pbix file from this link: Last month submitted based on id

     

    Best Regards,

    Rico Zhou

     

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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi giordafrancis 

    You can achieve your goal by build a measure or build calculated tables.

    Measure:

     

    Measure = 
Var _MaxDatePerID = MAXX(FILTER(ALL('Table'),'Table'[id]=MAX('Table'[id])),'Table'[month submitted])
Return
IF(MAX('Table'[month submitted])=_MaxDatePerID,1,0)

     

    Add the measure into table visual's Filter Field and set the measure to show items when the value =1.

    Result is as below.

    Calculated Table:

     

    Subset = CALCULATETABLE('Table',FILTER('Table','Table'[month submitted] = MAXX(FILTER('Table','Table'[id]=EARLIER('Table'[id])),'Table'[month submitted])))
    Subset2 = SUMMARIZE(FILTER('Table','Table'[month submitted]=MAXX(FILTER('Table','Table'[id]=EARLIER('Table'[id])),'Table'[month submitted])),'Table'[id],'Table'[month submitted],'Table'[budget])

     

    Results are the same:

    You can download the pbix file from this link: Last month submitted based on id

     

    Best Regards,

    Rico Zhou

     

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