Question about groupings

[mterry]

I'm running into an issue with grouping data - I'd like to take the chronological order into account and not sure if it's possible. For instance, with the below data, when I group by amount and use min date it groups all amounts together, regarldess of date, which I get is supposed to happen. What I'd like to have happen, is have it go through the data chronologically and incorporate the date when it groups - see the second table below. Is it possible to do this in Power Query?

Date	Amount
1/1/2020	1000
2/1/2020	1500
3/1/2020	1500
4/1/2020	1000
5/1/2020	1500
6/1/2020	1500

Desired Result		Actual Result
First Date	Amount		Min Date	Amount
1/1/2020	1000		1/1/2020	1000
2/1/2020	1500		2/1/2020	1500
4/1/2020	1000
5/1/2020	1500

[v-yiruan-msft · Accepted solution]

Hi @mterry ,

You can create a calculated column as below to achieve it:

First Date = 
VAR predate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] < EARLIER ( 'Table'[Date] ) )
    )
VAR predAmount =
    CALCULATE (
        MAX ( 'Table'[Amount] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] = predate )
    )
RETURN
    IF ( predAmount <> [Amount], 'Table'[Date] )

It also can be achieved it by creating a measure, you can get all details in this sample pbix file.

Best Regards

Rena

[mterry]

Thank you both, that helped

[amitchandak]

@mterry , try like with a date table

if(sum(table[Amount])- CALCULATE(sum(table[Amount]),PREVIOUSDAY(Date[Date]))=0, blank(),sum(table[Amount]))

or

sumx(filter(Summarize(Date,Date[Date],"_1",sum(table[Amount]),"_2", CALCULATE(sum(table[Amount]),PREVIOUSDAY(Date[Date]))),[_1]<>[_2]),[_1])

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.