Marked as spam. If you believe this is an error, submit an abuse report.

[imjeed]

Hi all,,

my below post marked as spam, but it is not!

please your support:

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-Dax-help-to-sum-amounts-within-...

Regards,

[v-yilong-msft · Accepted solution]

Hi @imjeed ,

I create a table as you mentioned.

Then I create a calculated column.

DateTime = 
'Table'[Date] & " " & 'Table'[Time]

Next I create another calculated column.

DateTime Difference =
DATEDIFF (
    CALCULATE (
        MIN ( 'Table'[DateTime] ),
        ALLEXCEPT ( 'Table', 'Table'[Category] )
    ),
    'Table'[DateTime],
    MINUTE
)

Measure = 
CALCULATE(
    SUM('Table'[Amount]),
    FILTER(
        ALLEXCEPT('Table', 'Table'[Category]),
        'Table'[DateTime Difference] <= 30
    )
)

Finally I create a new table and here is the DAX code.

Table 2 = 
SELECTCOLUMNS(
    'Table',
    "Category", 'Table'[Category],
    "Measure", 'Table'[Measure]
)

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[imjeed]

Hi @ryan_mayu 

Thanks for your replay

row number 7 should not be included becuase it's more than 30 Min in same day

first transaction of category B is 1-2-2023 20:00

last transaction of category B is 1-2-2023 20:40

so the last transaction should not included becuase it's more than 30MIN

thanks in advnace again.

[ryan_mayu]

@imjeed 

then you can try this

Column =

VAR _start=CALCULATE(min('Table'[datetime]),ALLEXCEPT('Table','Table'[Category]))

return if (DATEDIFF(_start,'Table'[datetime],MINUTE)<=30,"y")

[ryan_mayu]

@imjeed 

you can combine date time column

datetime = 'Table'[Date]+'Table'[Time]

then create another column

Column =

VAR _last=maxx(FILTER('Table','Table'[Category]=EARLIER('Table'[Category])&&'Table'[datetime]<EARLIER('Table'[datetime])),'Table'[datetime])

return if(ISBLANK(_last),"y",if(DATEDIFF(_last,'Table'[datetime],MINUTE)<=30,"y"))

then sum the amount and filter column to y