Account amount of the last date NOT the last amount for all accounts

[SaulM]

Hello everybody,

I have been scratching my head for three days now and looked for any example on all forms and could not find anything for what seems something so simple. I have two data tables (Fact and Department) and the Dates table for time intelligence. I don't think the "Dates" table will play a part in this, but I could be wrong. I have Account Numbers; Regions; Department; CertDate; and Amount in the Fact table.

What I am looking to do is write a DAX expression that gives me the amount for ANY acount number that has the last Certified Date "CertDate" NOT the last amount of the last "CertDate". I used the following Measures, but the below gives me the amount of the account which it was the last "CertDate".  My last added data was from February 2021, so I should see only CertDate = 02/28/2021.

This month, I will add March 2021 data and I should only see amounts for the Account Numbers for CertDate = 3/31/2021 only.

Relationship between the Fact and Department table is "Many to Many" with Cross filter direction = Both (Account Number to Account Number)

Relationship between Dates and Fact table is "Many to one" with Cross filter direction = Single (Date to CertDate)

Max Date = MAXX('Fact', 'Fact'[Cert Date])

Max Date2 = CALCULATE(MAX(Fact[Cert Date]), FILTER(ALL('Dates'[Date]), 'Dates'[Date]=LASTNONBLANK('Dates'[Date], [Total Amount])))

Total Amount of Last Month =

CALCULATE( SUM(Fact[Amount]),

FILTER( ALL( 'Fact' ),

'Fact'[Cert Date] = MAXX( 'Fact', Fact[Cert Date] ) ) )

What I am getting:

Region	Department	Account Number	Max Date	Total Amount of Last Month
Region 1	Department 1	10010	6/20/2020 0:00	$6.40
Region 1	Department 1	10011	3/20/2020 0:00	$6.70
Region 2	Department 2	10012	12/15/2020 0:00	$3.70
Region 5	Department 5	10015	5/13/2020 0:00	$9.10
Region 4	Department 6	17191719	2/28/2021 0:00	$1.00
Region 1	Department 4	474747	2/28/2021 0:00	$5.30
Region 2	Department 3	565656	2/28/2021 0:00	$7.50

What I need (anything with the Cert Date "Max Date" of the last date of the last month) In my case 02/28/2021):

Region	Department	Account Number	Max Date	Total Amount of Last Month
Region 4	Department 6	17191719	2/28/2021 0:00	$1.00
Region 1	Department 4	474747	2/28/2021 0:00	$5.30
Region 2	Department 3	565656	2/28/2021 0:00	$7.50

Thank  you all for the help!

[Anonymous · Accepted solution]

@SaulM 

It'll be hard to say what's wrong without seeing any kind of data and model but I can tell you right away that having a many-to-many between Department and the fact table is not correct. The many-to-many relationship exists in PBI in order to solve a very specific problem: the granularity issue. I don't think you've got this issue between your tables. A good model is one where all relationships are one-to-many between the dimensions and the facts. You can have crossfiltering on some very specific relationships where there is a true bridge table needed to implement the real many-to-many relationship. On top of that, crossfiltering is a very dangerous and treacherous thing. Should only be used in very specific scenarios in which you know exactly why it's needed. Most people use such constructs not because they know why they should be there but because they can. This is a big mistake that very often leads to DAX that produces numbers that can't be explained.

Can you please post the model and some data to work with?

[SaulM]

@Anonymous you are correct on the Many to Many Cardinality, I did not want that, in-fact it was automatically selected by Power BI, I tried to change it, but it would not allow me to do it as I tried to change the Cardinality and the “Cross filter direction” would be greyed out.

I found the fix!

For others to learn from my mistakes:

1st, the mistake that I failed to do is change the order, I should have changed the order of the “Fact” table first and then “Department” and then change it to “Many to one” and “Single”. I guess I was too focused on the DAX formula. This is now Fixed! Thanks!

2nd, I made Max Date2 = CALCULATE( MAX( Fact[Cert Date] ) , FILTER( ALL( 'Dates'[Date] ) , 'Dates'[Date] = LASTNONBLANK( 'Dates'[Date] , [Total Amount] ) ) )

Should have been Max Date2 - Harsh - 1 = CALCULATE( MAX( Fact[Cert Date] ) , FILTER( ALL( 'Dates'[Date] ) , 'Dates'[Date] = LASTNONBLANK( 'Fact'[Cert Date] , [Total Amount] ) ) )

I should have refrenced the 'Fact'[Cert Date] field after "LASTNOBLANK" as I am trying to find the MAX "Cert Date" and not the MAX 'Dates'[Date].

Or really use "LASTDATE" instead of "LASTNOBLANK": Max Date2 = CALCULATE( MAX( Fact[Cert Date] ) , FILTER( ALLSELECTED( 'Dates' ) , 'Dates'[Date] = LASTDATE( 'Fact'[Cert Date] ) ) )

Thanks again Daxer for pointing out the danger of Cordinality: Many to Many.