Group By with DateDiff for a Record with Multiple Transactions

[ja1meee]

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

[Dangar332]

Hi, @ja1meee 

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.

[ja1meee]

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

[Dangar332]

hi, @ja1meee 

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 

c

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.

[Dangar332]

hi, @ja1meee 

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

[ja1meee]

Hi @Dangar332 I am returning an error:

[Dangar332 · Accepted solution]

hi, @ja1meee 

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

[ja1meee]

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:

[Dangar332]

hi, @ja1meee 

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