DAX - Get Last Amount Value for Every ID Against Every Date

[PBI_Member_01]

Greetings PowerBI Community,

I have a DirectQuery dataset in which I require to make two calculated columns. The Data I have on hand looks something like this:

What I am trying to achieve is to get two new calculated columns 'Last Trans Amount' and 'Last Trans Date' and my expected output would look something like this:

In short, I want to create two seperate columns from the Original Date and Amount columns, which will give me the Last Amount that was generated on the specific dates.
What I have figured so far is I may have to use the Calculate function along side RANKX, but I am not so familiar with the filter functions that may be required to achieve this.
I have looked at some other valuable community posts related to my issue and tried implementing it but the outcome is not close to my desired result or maybe I am doing something wrong.

I am a beginner Power BI User and have been trying to get my head around this. Any help or solution regarding this would be highly appreciated.

Many Thanks in Advance.

Regards.

[PBI_Member_01]

So just to update, This works perfectly fine. Thank you for your help on this.
I have a small question on this, what if I want to summarize this on the Transaction Month level instead of every single date. Will this be possible to achieve with HASONEVALUE filter or Do I need to modify this solution in a completely different way.

I do have a Calendar Date Table for my respective Transaction Table. Can that be used in any way to shift the grain to Month level?

Thanks in advance.

Regards

[Jihwan_Kim]

Hi,

Thank you for your feedback.

In the current DAX measure, it checks the last Trans_ID for each date.

If you want to show month level, I think a new measure has to check the last Trans_ID and the last-non-blank date in each month.

Hasonevalue(date) is only to achieve not to show total row in the visualization, because I did not know what to show on the total row.

I hope this helps to further proceed.

Thank you.

[PBI_Member_01]

Hi @Jihwan_Kim 

Thank you for giving your valuable insight on this.

I implemented the DAX measure the way you had proposed the solution but with some slight modification since it was not displaying the latest transaction record. I have attached the image below as the output I am getting:

So what I did is since the Trans Date column was still showing me seperate values. I tried to take it up to the month level by Using the Month with Year column from Calendar for respective table, this is what I did:

Now this DAX Calculation gives me the above result set which I have attached, Can you please validate if this is the correct approach that I am using.
And secondly, I do want to show the total of Last Transaction Amount at the end.

I am a beginner and this may seem fairly easy, so apologies in advance. Any help regarding this would be great.

Sorry for the trouble and Big thanks in advance !

Regards

[Jihwan_Kim]

Hi,

Thank you for your message, but sorry that I cannot check by only seeing the partial screenshot of what you are getting.

[PBI_Member_01]

Hi @Jihwan_Kim,

Attaching image below:

The last column is what I have created with DAX Measure. But it shows the same value as the original Transaction Amount.
It would be ideal if I can get this to Show at month level, the last transaction amount, along with another column 'Last Transaction Date' showing that specific date on which the last Transaction Amount was made.
The Month with Year is from the Calendar of this specific table.

Let me know if you need more info on this.

Big Thanks once again.

Regards

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file whether it suits your requirement.

Last transaction amount: =
VAR _lastdate =
    MAX ( Data[Transaction Date] )
RETURN
    IF (
        HASONEVALUE ( Data[Month-Year] ),
        SUMX (
            FILTER ( Data, Data[Transaction Date] = _lastdate ),
            Data[Transaction Amount]
        )
    )

[PBI_Member_01]

Hi @Jihwan_Kim ,

Thanks again for your effort on this.

This does give me the last Transaction Amount value for the given month, But just like in your image, It does not show the Sum of Total value at the end of the column.
Can you please help me in displaying this amount as well?

Also, If I need to display another column after this Transaction amount, which displays the Transaction Date on which this Last Transaction Amount was made, How can that be achieved?

You've been a great help so far and I appreciate you putting your time and effort into this.

Big thanks once again.

Kind Regards

[PBI_Member_01]

Just a small update. I have managed to get Last Transaction Date by using  MAXX and RELATEDTABLE functions.
I, Only need to show the total for Last Transaction Amount Column which has been created by using your above mentioned DAX.

I tried removing the HASONEVALUE function from your DAX and it does show me the total value at the end but It is an incorrect value as you can see in the image below:

Any suggestion on how this can be fixed to show the correct value?

Thanks for all the help so far.

Kind Regards

[Jihwan_Kim]

Hi,

If your current measure does not include Hasonevalue function, please try to replace your current measure with the below.

New Measure: =
SUMX (
    VALUES ( Calendartable[Month With Year] ),
    [Last Transaction Amount Measure without hasonevalue function:]
)

[PBI_Member_01]

Hi @Jihwan_Kim 

I tried making a second measure just like you suggested. This is the output I am getting:

But if you add these numbers up, there is a difference of -40 (Actual total -> 6317.63). Still not sure why it is not giving the correct total.

[Jihwan_Kim]

Could you please share your sample pbix file's link here? And then I can try to look into it to come up with a more accurate solution.

By the way, how is your [Last Payment Amount] measure? Is it showing the correct number for each month? (besides the total row in the visualization).

Thank you.

[PBI_Member_01]

Hi @Jihwan_Kim 

Sorry I have been working on the Production Machine for this case. I have generated a sample pbix on my end but I am unable to upload as it gives me error here ".pbix is not supported"
Also, to answer your recent questions, the Last Payment Amount is a second DAX measure which uses the first DAX Measure Last Transaction Amount (as suggested by you). The 2nd measure Last Payment Amount shows the correct values just like Last Transaction Amount from what I have seen (as shown in above attached image)
The totals are just not adding up.
I have attached a sample file in the link replicating the case on dummy data. Please see if there is something that can be done about this or a better way to approach this.

https://file.io/j2tIJMP5uLH3 

Highly appreciate your effort and insight on this.

Big Thanks in advance.

Kind Regards

[Jihwan_Kim]

Hi,

Thank you for your message.

I think there is one more column that needs to be considered besides the month-year column. 

Sometimes, sending a partial screenshot makes it difficult to come up with an expected result.

I am still not sure how this will work in your real dataset, because you mentioned that it is a direct query mode and I cannot know how the whole relationship in the data model looks like, but hopefully this will work for your real dataset as well.

[PBI_Member_01]

Hi @Jihwan_Kim

I have been trying to post a response to your latest message but somehow when I press the Reply button, nothing happens, neither my reply gets submitted nor do I get any error. Anyways...

Based on the file you shared, I managed to tweak the second measure to show the exact total amount at column level.

To Summarize the input you provided and how the complete solution worked, First measure is created to calculate the last amount value made on a specific date (This was later changed to Last amount value for a given Month)

Last Amount Value = 
VAR _EndDate =
    MAX (TBL_Transaction[TransDT])
RETURN
    SUMX (
            FILTER(TBL_Transaction, TBL_Transaction[TransDT] = _EndDate),
            TBL_Transaction[TransAmount]
         )

Now for every month and for every row, last amount value was being fetched correctly but the total sum at column level was not correct so to fix that, we created a second measure to cater this problem and summarized it by ID and Month.

Fixed Last Amount Value = 
SUMX (
    SUMMARIZE (
        TBL_Transaction,
        TBL_Transaction[CardID],
        TBL_Transaction[Month-Year]
    ),
    [Last Amount Value]
)

This second measure gives me the correct result now.

Thank you for helping and providing continuous support on this issue.

Regards

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new calculated table.

New Table =
SELECTCOLUMNS (
    CALCULATETABLE (
        Data,
        TREATAS (
            GROUPBY (
                Data,
                Data[Account_ID],
                Data[Trans_Date],
                "@lasttrans", MAXX ( CURRENTGROUP (), Data[Trans_ID] )
            ),
            Data[Account_ID],
            Data[Trans_Date],
            Data[Trans_ID]
        )
    ),
    "Account_ID", Data[Account_ID],
    "Last_Trans_Date", Data[Trans_Date],
    "Last_Trans_Amount", Data[Trans_Amount]
)

[PBI_Member_01]

Hi @Jihwan_Kim ,

Thank you for your quick response on this.

I checked your file and the solution you devised. It does match the expected result.
However, When creating this table, it switches the Storage Mode from DirectQuery to Mixed and on having discussion with my Reporting Manager, what we want is to keep the Storage Mode at DirectQuery and somehow handle this case with Calculated Measures or Columns.
To my understanding, when we create a seperate calculated table like this, it switches the Storage Mode, which is of quite significance to the company.

I appreciate your solution on this but can you give me any insight if this is achievable with the help of Calculated Measures or Columns created within the same table where rest of the columns reside?

Thank you once again.

Kind Regards

[Jihwan_Kim]

Hi,
Thank you for your feedback, but I am not sure how your expected outcome of a visualization or a calculated columns look like.

Please check the below picture and the attached pbix file.

It is for creating a measure.

Last trans amount measure: =
VAR _lasttransID =
    MAX ( Data[Trans_ID] )
RETURN
    IF (
        HASONEVALUE ( Data[Trans_Date] ),
        SUMMARIZE ( FILTER ( Data, Data[Trans_ID] = _lasttransID ), Data[Trans_Amount] )
    )