Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a table with data below. I have a date slicer which is filtering the date in this table. Based on the date range selected I need to filter my table in such a way that for each company
and type M it brings the smallest and largest date and corresponding amount and excludes other type M records. Rest all the type records(X,Y) should appear as it is.
Basically 2 records of type M for each company and the first record should have the amount negative.I want to create a calculated table based on this.Is it achievable in DAX?
Input table
group | company_name | Type | Date | Amount |
AA | A | X | 28-02-2019 | 100 |
AA | A | Y | 27-03-2019 | 200 |
AA | A | M | 27-04-2019 | 300 |
AA | B | X | 10-05-2019 | 400 |
AA | A | M | 29-04-2019 | 500 |
AA | A | M | 15-05-2019 | 500 |
AA | B | M | 10-06-2019 | 600 |
AA | B | M | 11-06-2019 | 700 |
AA | B | M | 20-06-2019 | 800 |
Output Table:
group | company_name | Type | Date | Amount |
AA | A | X | 28-02-2019 | 100 |
AA | A | Y | 27-03-2019 | 200 |
AA | A | M | 27-04-2019 | -300 |
AA | B | X | 10-05-2019 | 400 |
AA | A | M | 15-05-2019 | 500 |
AA | B | M | 10-06-2019 | -600 |
AA | B | M | 20-06-2019 | 800 |
Thank you!
Solved! Go to Solution.
Hello @valentina14
you could use this formula to create a new table
New Table =
FILTER('Table','Table'[Type]<>"M"||
('Table'[Type]="M"&&
('Table'[Date]=CALCULATE(MIN('Table'[Date]),FILTER('Table','Table'[group]=EARLIER('Table'[group])&&'Table'[company_name]=EARLIER('Table'[company_name])&&'Table'[Type]="M"))||
'Table'[Date]=CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[group]=EARLIER('Table'[group])&&'Table'[company_name]=EARLIER('Table'[company_name])&&'Table'[Type]="M")))))
Result:
and here's the sample pbix file, please try it out.
Best regards
Lin
Hello @valentina14
you could use this formula to create a new table
New Table =
FILTER('Table','Table'[Type]<>"M"||
('Table'[Type]="M"&&
('Table'[Date]=CALCULATE(MIN('Table'[Date]),FILTER('Table','Table'[group]=EARLIER('Table'[group])&&'Table'[company_name]=EARLIER('Table'[company_name])&&'Table'[Type]="M"))||
'Table'[Date]=CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[group]=EARLIER('Table'[group])&&'Table'[company_name]=EARLIER('Table'[company_name])&&'Table'[Type]="M")))))
Result:
and here's the sample pbix file, please try it out.
Best regards
Lin
@Anonymous , Add these two measure in place of date and amount in visual
firstnonblank(Table[Date], blank())
firstnonblankvalue(Table[Date], max(Table[Amount]))
And check
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |