Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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
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
Thank you both, that helped
@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.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |