topic Re: Group By with DateDiff for a Record with Multiple Transactions in DAX Commands and Tips

Group By with DateDiff for a Record with Multiple Transactions

Anonymous — Sat, 21 Oct 2023 03:13:09 GMT

Hi PowerBI experts!

I'm relatively new on using GROUPBY and I am confused on how it works. Apologies for the lack of knowledge.

Problem: I am building a report in which an account number has multiple transactions. The values that were provided are as follows.
A. Base Date - a date that is static for every account number;
B. Transaction Dates - the dates that are logged everytime an account number has made a transaction;
C. Account Number - The # of account numbers is atleast 40k that is why I'm not able to use LOOKUPVALUE for this one.

Screenshot A:

STEPS DONE
Step 1. I have managed to create a DATEDIFF to calculate the difference between the BASE DATE and TRANSACTION DATE. In this way, I was able to check which TRANSACTION DATE is closest to the BASE DATE.

DayDiff = DATEDIFF('Sample'[Date],'Sample'[Transaction Date],DAY)

Screenshot B

Step 2. Next step is to create a MIN field for the datediff. This is where I am currently stuck at. The calculation I made for MIN DAYDIFF treats each row as an individual record. I'm sure that I should be using the GROUPBY calculation here but I'm not sure on how it generally works.

I

Min DayDiff = min('Sample'[DayDiff])
Screenshot C:

What I want to achieve: The goal of this report is to create a flag field in which it will tell the user that this TRANSACTION DATE of an ACCOUNT NUMBER is the one closest to the value of the BASE DATE.
I have the logic but I am lacking the execution part.

Logic: MIN(DayDiff) GROUPBY member acc

Screenshot 😧

Step 3. After creating the MIN DAYDIFF. I need to create a calculation with the following logic. This will serve as the flag in which I will be able to continue developement of the report. I also noted that there are some TRANSACTION DATE that are not matching with BASE DATE, so I need to get its MIN value. (see screenshot E)

[FLAG] = IF ( 'Table'[DAYDIFF] = 'Table'[MIN DAYDIFF] , 1, 0)

Screenshot E

I really appreciate for any suggestions on how to build this one. Thank you!

-James

Re: Group By with DateDiff for a Record with Multiple Transactions

Dangar332 — Sat, 21 Oct 2023 06:39:57 GMT

Hi, Anonymous

Try below code for

Just adjust your table and column name

Min DayDiff =

Calculate (min('Sample'[DayDiff]),

allexcept('yourtablename',

'yourtablename'[member acc]))

These will help that you want

After your flag logic work

[FLAG] = IF ( 'Table'[DAYDIFF] = 'Table'[MIN DAYDIFF] , 1, 0)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. and don't forget to give kudos.

Re: Group By with DateDiff for a Record with Multiple Transactions

Anonymous — Sat, 21 Oct 2023 12:01:14 GMT

Hi Dangar332 ! The Min DayDiff worked wonders for me. Thank you! However I am having an issue on the flag. Kindly look on the calculation I made for the flag calculation.

Flag =
var _DayDiff = CALCULATE(

min('Sample'[DayDiff]),

ALLEXCEPT('Sample','Sample'[Member Acc]))

var _result =

IF(

[Min DayDiff] = _DayDiff && NOT(ISBLANK(_DayDiff)),1,0

)

return

_result

Using that calculation for the flag returns me this result:.

For reference, this is the result that I want to achieve:

Thank you

-James

Re: Group By with DateDiff for a Record with Multiple Transactions

Dangar332 — Sat, 21 Oct 2023 12:39:44 GMT

hi, Anonymous

Flag =
var _DayDiff = CALCULATE(

min('Sample'[DayDiff]),

ALLEXCEPT('Sample','Sample'[Member Acc]))

var _result =

IF(

[Min DayDiff] = _DayDiff && NOT(ISBLANK(_DayDiff)),1,0

)

return

_result

in above code you compare [Min DayDiff] with _DayDiff

both are identical to each other that's why it give you 1 in all rows

try below

var a = DATEDIFF('Sample'[Date],
'Sample'[Transaction Date],DAY
)

var b =

CALCULATE(

min(a),

ALLEXCEPT('Sample','Sample'[Member Acc])

)

var c = IF(

a =b
&& NOT(ISBLANK(a))
,1,0

)

return

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. and don't forget to give kudos.

Re: Group By with DateDiff for a Record with Multiple Transactions

Dangar332 — Sat, 21 Oct 2023 13:28:39 GMT

hi, Anonymous

these is updated code

try below

falg

var daydiff = DATEDIFF('Sample'[Date],
'Sample'[Transaction Date],DAY
)

var mindaydiff =

CALCULATE(

min(daydiff),

ALLEXCEPT('Sample','Sample'[Member Acc])

)

var result = IF(

daydiff = mindaydiff
&& NOT(ISBLANK(daydiff))
,1,0

)

return

result

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. and don't forget to give kudos

Re: Group By with DateDiff for a Record with Multiple Transactions

Anonymous — Sun, 22 Oct 2023 08:31:15 GMT

Hi Dangar332 I am returning an error:

Re: Group By with DateDiff for a Record with Multiple Transactions

Dangar332 — Sun, 22 Oct 2023 14:06:01 GMT

hi, Anonymous

are you create column or measure for daydiff and min day diff?

Re: Group By with DateDiff for a Record with Multiple Transactions

Dangar332 — Sun, 22 Oct 2023 16:35:03 GMT

hi, Anonymous

try below code

result =

var a = CALCULATE(MIN('Table'[day diff]),ALLEXCEPT('Table','Table'[member acc]))

var b = IF(MIN('Table'[day diff]) = a && NOT(ISBLANK(MIN('Table'[day diff]))),1,0)

return b

Here

'Table'[day diff] is calculated column and code is below

day diff = DATEDIFF('Table'[base date],'Table'[transaction date],day)

for download .pbix file of this solution click HERE

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. and don't forget to give kudos

Re: Group By with DateDiff for a Record with Multiple Transactions

Anonymous — Sun, 22 Oct 2023 23:33:45 GMT

Thank you very much Dangar332! This works very well in the report. For reference, the DAYDIFF field I created was a calculated column.

Please see result on my end: