cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SaulM
Frequent Visitor

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

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:
RegionDepartmentAccount NumberMax DateTotal Amount of Last Month
Region 1Department 1100106/20/2020 0:00$6.40
Region 1Department 1100113/20/2020 0:00$6.70
Region 2Department 21001212/15/2020 0:00$3.70
Region 5Department 5100155/13/2020 0:00$9.10
Region 4Department 6171917192/28/2021 0:00$1.00
Region 1Department 44747472/28/2021 0:00$5.30
Region 2Department 35656562/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):
RegionDepartmentAccount NumberMax DateTotal Amount of Last Month
Region 4Department 6171917192/28/2021 0:00$1.00
Region 1Department 44747472/28/2021 0:00$5.30
Region 2Department 35656562/28/2021 0:00$7.50

 

Thank  you all for the help!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@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?

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

@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?

@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.

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors