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

ITSaulGood · April 23, 2021, 5:45pm

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!
MaxDate.pbix (90.2 KB)

Harsh · April 23, 2021, 6:56pm

Hello @ITSaulGood,

Thank You for posting your query onto the Forum.

Well firstly, I would like to say that it’s absolutely not recommended to have “Many-to-Many” since it’s results in ambiguity and provides vague results. I’m providing the link of the article as well videos on that. And rather than creating “Many-to-Many” relationship you can go for the TREATAS function. Also if required, you can also go through the course of “Data Modelling and Transformations” course.

Now, moving onto the solution part.

I’ve changed the “Many-to-Many” relationship to the “One-to-Many” status. Below is the screenshot of the model provided for the reference.

Once this is done, I checked out your measures. And this is the measure you’ve written for the “Max Date” -

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

Now, the is problem is in the last part of referencing i.e. under LASTNONBLANK you’ve referenced Dates[Date] but since your condition is to retrieve the MAX date from “Cert Date” field so it should actually be that under the LASTNONBLANK and not Date[Date].

Here’s my formula to achieve the Max Date i.e. Max Cert Date -

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

Or rather than using LASTNONBLANK you could’ve simply used LASTDATE -

Max Date2 - Harsh - 2 = 
CALCULATE( MAX( Fact[Cert Date] ) , 
    FILTER( ALLSELECTED( 'Dates' ) , 
        'Dates'[Date] = LASTDATE( 'Fact'[Cert Date] ) ) )

Now, to calculate the “Total Value” results based on the Last Date of Cert again you had referenced a wrong field in your measure. Below is your measure -

Total Value of Last Month = 
CALCULATE( SUM(Fact[Amount]),
    FILTER( ALL( 'Fact' ), 
        'Fact'[Cert Date] = MAXX( 'Fact', Fact[Cert Date] ) ) )

So here, you referenced everything from the Fact Table whereas the formula should have been like this -

Total Value of Last Month - Harsh = 
CALCULATE( SUM( Fact[Amount] ) ,
    FILTER( ALLSELECTED( Dates ) , 
        Dates[Date] = MAXX( 'Fact', Fact[Cert Date] ) ) )

Now, if you check the results it’ll evaluate the results based on the “Cert Last Date”. Below is the screenshot provided for the reference -

Important Note: You had mentioned in your post, that Region 4 will only have Department 6 as the result but in actual Region 4 has two departments that is, Department 4 and Department 6. Since both these departments have the last date as 28th Feb, 2021. And therefore two departments will be showcased for the same region in this case. Also Region 3 , Department 2 has the Max Cert Date as 28th Feb, 2021.

I’m also attaching the working of the PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

MaxDate - Harsh.pbix (92.7 KB)

deltaselect · April 23, 2021, 7:20pm

Hi ITSaulGood,

it seems that the problem is already solved by Harsh (not fast enough )

I agree about his comments about Many to Many filter, better one to many.

I made a Dax formula which works fine as well: (assuming you always need End of month (balance account) figures)

Total amount as end of month = CALCULATE( [Total Amount], FILTER(‘Fact’, ‘Fact’[Cert Date] =EOMONTH(‘Fact’[Cert Date],0)))

One remark is that filtering in large fact-tables makes the calculation slow.

It gives the following table:

ITSaulGood · April 23, 2021, 8:29pm

WOW @Harsh and @deltaselect. Thank you both for the quick response! @Harsh , you are corrected 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.

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.

2nd, I completely missed the “Max Date” measure referencing Dates[Date] and not [CertDate] (LLASTNONBLANK( ‘Dates’[Date] , [Total Amount] ), I should have caught that, but I like the “LASTDATE” function better.

3rd, on the “Total Value of Last Month” measure, I stayed with the thought that I should use the ‘Fact’ table only as I’m trying to find the MAX date for the “CertDate”. I did not want to pickup the MAX Date from the Date table as it has future dates up to 12/2021. Is best practice here to FILTER ALL the Dates form the ‘Dates’ table regardless and explicitly trying to find the MAX in the ‘Fact’, Fact[CertDate]?

Thank you again.
Saul

Harsh · April 23, 2021, 8:45pm

Hello @ITSaulGood,

You’re Welcome.

I’m glad that you found the solution helpful.

So for the last query i.e 3rd point - “on the “Total Value of Last Month” measure, I stayed with the thought that I should use the ‘Fact’ table only as I’m trying to find the MAX date for the “CertDate”. I did not want to pickup the MAX Date from the Date table as it has future dates up to 12/2021. Is best practice here to FILTER ALL the Dates form the ‘Dates’ table regardless and explicitly trying to find the MAX in the ‘Fact’, Fact[CertDate]?”

If you the see the last line of my formula I’ve used the Fact Table field and not the Date field inside the MAX. The logic here is like this -

The Date from the Date table should be equal to the last date in the Fact Table of the Cert Date. So now, it doesn’t matter whether you’ve dates till 31st December, 2021 or 31st December, 2030. It’ll consider the values only till last date from your fact table and not from Dates table.

Hoping this answer your last query as well and you find this useful and helpful.

Thanks and Warm Regards,
Harsh

ITSaulGood · April 23, 2021, 8:51pm

@Harsh Changing the Cardinality works on the sample data I sent flawlessly. On my real pbix data model, it will not allow me to change the relationship from “Many to Many” to “One to Many” or “Many to One”. I tried to changing the order of the tables and then tried again, but no go.

Harsh · April 23, 2021, 8:59pm

Hello @ITSaulGood,

With looking at the model the members of the forum won’t be able to judge and provide the results in a better and efficient manner.

If your data contains sensitive information than you can mask the data and provide the mock-up PBIX file so that the members can look into it. Below is the link provided where it shows how to mask the sensitive data.

Because without looking at the data it’ll just be a guess work for the forum members and will the increase the length of the thread.

Also since it’s a new question pertaining to the data model. I would suggest you to please create a new thread for it. So that it increases the visibility of the question since this thread has been solved.

Thanks and Warm Regards,
Harsh