Calculate using the Latest Revision

[mdrew9]

Struggling with a DAX measure that is the base of many other measures.

I have a budget, which has multiple revisions. Its formula right now is calculate(sum(BudgetValue),Department<>Accounting,Group<>Finance)
This works fine, but since I have added a second revision the table now has both the Rev 0 budget values and the Rev 1 budget values.

calculate(sum(BudgetValue),Department<>Accounting,Group<>Finance,Max(Revision)) - This is what I wanted to do, doesn't work...

Thanks in advance.

 

[miguel.escobar]

Struggling with a DAX measure that is the base of many other measures.

I have a budget, which has multiple revisions. Its formula right now is calculate(sum(BudgetValue),Department<>Accounting,Group<>Finance)
This works fine, but since I have added a second revision the table now has both the Rev 0 budget values and the Rev 1 budget values.

calculate(sum(BudgetValue),Department<>Accounting,Group<>Finance,Max(Revision)) - This is what I wanted to do, doesn't work...

Thanks in advance.

CALCULATE( SUMX(SUMMARIZE( put some columns in here, add the MAX aggregate here), [name of the aggregate]), Department<>Accounting,Group<>Finance,Max(Revision) )

and that should do the trick. To know what columns to use we need to get a closer look at your data model but Revision should be one of them.

Best!
Miguel

 

[mdrew9]

Thanks for the reply. I put in a work around where I did a simple sql query to my source data to get the max revision for each project, then related the two, compared the Rev in my source file and now filter on "Latest Rev". But I will try to see how this works. Thank you.