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.
Hi Experts
How would you do a summarised table and or a measure and or calculated column to give you the last purchased date each month based on the customer in first column
Customer | Date |
abc | 01/01/2022 |
abc | 27/01/2022 |
abc | 02/03/2022 |
abc | 22/03/2022 |
abc | 03/06/2022 |
abc | 22/06/2022 |
ddd | 01/01/2022 |
ddd | 27/01/2022 |
ddd | 02/03/2022 |
ddd | 22/03/2022 |
ddd | 03/06/2022 |
ddd | 22/06/2022 |
Solved! Go to Solution.
@Anonymous Please create a column with below code:-
Column =
VAR _mon =
MONTH ( [Date] )
VAR _year =
YEAR ( [Date] )
RETURN
CALCULATE (
MAX ( [Date] ),
FILTER (
ALL ( 'Table (7)' ),
MONTH ( [Date] ) = MONTH ( EARLIER ( 'Table (7)'[Date] ) )
&& YEAR ( [Date] ) = YEAR ( EARLIER ( 'Table (7)'[Date] ) )
&& 'Table (7)'[Customer] = EARLIER ( 'Table (7)'[Customer] )
)
)
Now use this column on your visual
Thanks,
Samarth
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
Hi @Anonymous ,
You can create a measure like below:-
_measure =
CALCULATE (
MAX ( 'Table (7)'[Date] ),
FILTER (
ALL ( 'Table (7)'[Date] ),
MONTH ( 'Table (7)'[Date] ) = MONTH ( SELECTEDVALUE ( 'Table (7)'[Date] ) )
&& YEAR ( 'Table (7)'[Date] ) = YEAR ( SELECTEDVALUE ( 'Table (7)'[Date] ) )
)
)
Output:-
Thanks,
Samarth
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
Hi Samarth - I just want to show the max date for each month - Not all the data.... please..
end result
Customer | Date |
abc | 27/01/2022 |
abc | 22/03/2022 |
abc | 22/06/2022 |
ddd | 27/01/2022 |
ddd | 22/03/2022 |
ddd | 22/06/2022 |
@Anonymous Please create a column with below code:-
Column =
VAR _mon =
MONTH ( [Date] )
VAR _year =
YEAR ( [Date] )
RETURN
CALCULATE (
MAX ( [Date] ),
FILTER (
ALL ( 'Table (7)' ),
MONTH ( [Date] ) = MONTH ( EARLIER ( 'Table (7)'[Date] ) )
&& YEAR ( [Date] ) = YEAR ( EARLIER ( 'Table (7)'[Date] ) )
&& 'Table (7)'[Customer] = EARLIER ( 'Table (7)'[Customer] )
)
)
Now use this column on your visual
Thanks,
Samarth
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
Hi @Anonymous It would be very helpful if you could share the snapshot of expected output?
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
Hi @Anonymous
Can you try the following as a new table. Apologies, am on phone so can't test syntax.
Table =
SUMMARIZE ( 'Table', 'Table'[Customer], "MaxDate", MAX ( 'Table'[Date] ) )
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
@Anonymous did you try my solution?
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
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 |
---|---|
105 | |
97 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |