Forum Discussion
Help with dax (most recent date)
Hello!
I need to create a calculated column that does the following:
Check if the reference (Second column) is repeated in the same month, and if it is repeated, bring the most recent one.
Can you help me?
12 Replies
yforti13 What do you mean "bring the most recent one" ? The most recent date, reference??
Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.What do you mean "bring the most recent one" ? The most recent date, reference??
-> Yes, the most recente reference.The references (2 column) are repeated several times.
The new column needs to calculate:
1st -> If the reference is repeated in the same month.
2nd -> If it is repeated, bring the most recent reference based on the date.
For example:in this case the reference is repeated, so the expected value is to bring the reference only in the line with the most recent date
Like this:
Did you understand?yforti13
Please check if this works for you?New Ref = VAR __Refe = [Referencia] VAR __Date = [DataTransacaoHUB] VAR __Month = FILTER( ALLNOBLANKROW(Table01), FORMAT([DataTransacaoHUB],"mmyy") = FORMAT( __Date,"mmyy") && [DataTransacaoHUB] > __Date && [Referencia] = __Refe ) RETURN IF( ISEMPTY( __Month) , __Refe )I modifed your data to test.
Hi yforti13 ,
What's the situation now? If this issue has been resolved, consider accepting helpful replies as solutions.
Best regards,
Rimmon Li
With a small sample of data the solution worked. The problem is that my data set is very large, so the solution gives the result: out of memory. Is it possible to do the solution in Power Query?
Hi yforti13 ,
Thanks for the reply.Yes, moving this logic to Power Query is a good idea
Add Date-Only and Month Columns
Duplicate the 'Date' column if needed
Power Query
= Table.AddColumn(PreviousStep, "Year-Month", each Date.ToText([Date Only], "yyyy-MM"), type text)//Group by Year-Month and Reference
= Table.Group(
PreviousStep,
{"Year-Month", "Reference"},
{
{"MaxDate", each List.Max([Date]), type datetime},
{"AllRows", each _, type table}
}
)//After grouping, expand the nested tables to bring back original rows. Then, compare each row’s Date with the MaxDate for that group.
and now add column to mark most recent entry
= Table.AddColumn(PreviousStep, "Result Ref", each if [Is Most Recent] then [Reference] else null)
If this issue has been resolved, consider accepting helpful replies as solutions.
Regards,
Chaithra E.Hi yforti13 ,
We would like to follow up to see if the solution provided by us resolved your issue. Please let us know if you need any further assistance.
If our response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.Regards,
Chaithra.Hi yforti13 ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.Regards,
Chaithra EHi yforti13 ,
We would like to follow up to see if the solution provided by us resolved your issue. Please let us know if you need any further assistance.
If our response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.Regards,
Chaithra.
