Max value based on dynamic date filter

[itsmeanuj]

I need to calculate something similar to the "partition by" function in SQL. 

My data contains multiple instances of IDs. I need to create a measure that would pick up the latest value of the "Call_flag" column (first calculate max "created_on" and then max "Stamp" column) for each unique ID & Product Combination. This means that the total count should show the count of unique ID&Product combinations. e.g. for Product A, the count of IDs would be 4 with the split of 3 "1s" (ID = 242977761642152961, 929999783480788916, 932585828936911719) and 1 "0"( ID = 932778067030379386".  

if the latest (first calculate max "created_on" and then max "Stamp" column) ID product combination has a value of 1 for "Call_flag" then, consider 1 and similarly 0 if the latest value is 0

PRODUCT_NAME	ORGANIZATION_NAME	ID	CHANGED_ON	HAS_CALLS	IS_TREATING	CALL_FLAG	STAMP
Product A	A	242977761642152961	2020/04/20	0	1	1	2020/04/21
Product A	A	242977761642152961	2020/04/20	0	1	0	2020/04/20
Product A	A	242977761642152961	2020/03/20	0	0	1	2020/04/22
Product A	A	242977761642152961	2020/03/20	0	0	0	2020/03/20
Product A	B	929999783480788916	2021/05/03	1	1	1	2021/05/04
Product A	B	929999783480788916	2021/05/03	1	1	1	2021/05/03
Product A	B	929999783480788916	2021/04/03	0	0	1	2021/04/04
Product A	B	929999783480788916	2021/04/03	0	0	1	2021/04/03
Product A	D	932585828936911719	2023/09/02	0	1	1	2023/09/03
Product A	D	932585828936911719	2023/09/02	0	1	1	2023/09/02
Product A	D	932585828936911719	2023/02/02	1	1	1	2023/02/03
Product A	D	932585828936911719	2023/02/02	1	1	1	2023/02/02
Product A	C	932778067030379386	2022/07/16	0	1	0	2022/07/17
Product A	C	932778067030379386	2022/07/16	0	1	0	2022/07/16
Product A	C	932778067030379386	2022/04/16	1	0	0	2022/04/17
Product A	C	932778067030379386	2022/04/16	1	0	0	2022/04/16
Product B	A	242977761642152961	2020/04/21	0	0	1	2020/04/22
Product B	A	242977761642152961	2020/04/21	0	0	1	2020/04/21
Product B	A	242977761642152961	2020/03/02	0	0	1	2020/03/03
Product B	A	242977761642152961	2020/03/02	0	0	1	2020/03/02
Product B	B	929999783480788916	2021/05/31	1	1	0	2021/06/01
Product B	B	929999783480788916	2021/05/31	1	1	0	2021/05/31
Product B	B	929999783480788916	2021/04/08	1	1	0	2021/04/09
Product B	B	929999783480788916	2021/04/08	1	1	0	2021/04/08
Product B	D	932585828936911719	2023/09/10	0	0	0	2023/09/11
Product B	D	932585828936911719	2023/09/10	0	0	0	2023/09/10
Product B	D	932585828936911719	2023/02/22	1	1	0	2023/02/23
Product B	D	932585828936911719	2023/02/22	1	1	0	2023/02/22
Product B	C	932778067030379386	2022/07/10	0	0	1	2022/07/11
Product B	C	932778067030379386	2022/07/10	0	0	1	2022/07/10
Product B	C	932778067030379386	2022/04/14	0	1	1	2022/04/15
Product B	C	932778067030379386	2022/04/14	0	1	1	2022/04/14

[Anonymous · Accepted solution]

Hi  @itsmeanuj ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _maxdatecreateon=
MAXX(
    FILTER(ALLSELECTED('Table'),
    'Table'[PRODUCT_NAME]=MAX('Table'[PRODUCT_NAME])&&'Table'[ID]=MAX('Table'[ID])),[CHANGED_ON])
var _maxstamp=
MAXX(
    FILTER(ALLSELECTED('Table'),     'Table'[PRODUCT_NAME]=MAX('Table'[PRODUCT_NAME])&&'Table'[ID]=MAX('Table'[ID])&&'Table'[CHANGED_ON]=_maxdatecreateon),'Table'[STAMP])
return
MAXX(
    FILTER(ALL('Table'),    'Table'[PRODUCT_NAME]=MAX('Table'[PRODUCT_NAME])&&'Table'[ID]=MAX('Table'[ID])&&'Table'[CHANGED_ON]=_maxdatecreateon&&'Table'[STAMP]=_maxstamp),'Table'[CALL_FLAG])

count of IDs =
CALCULATE(
    DISTINCTCOUNT(
    'Table'[ID]),FILTER(ALL('Table'),'Table'[PRODUCT_NAME]=MAX('Table'[PRODUCT_NAME])))

2. Result:

Best Regards,

Liu Yang

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

[amitchandak]

@itsmeanuj , if you need a column you can use earlier

maxx(filter(Table, Table[ID] = earlier(Table[ID])) , Table[Date])

refer

Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8

Or you can consider the following for measures

Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

[itsmeanuj]

@amitchandak - Thanks for your response. But in my scenario, we first need to take latest "changed_on" date and then latest "Stamp" date in that "Changed_on" date. Also this needs to be dynamic as there is a "changed_on" date slicer at the top of the page.