Display max date value in table on type while selection of any date from slicer

[Anshenterprices]

Sample Data:-

Category	Sub Category	Value	Date	CDate	Type
Commodity	T1	19.43	9/29/2021	29-Sep-21	Broker
FNO	MF1	0.5	9/29/2021	29-Sep-21	Broker
FNO	B1	3.95	9/29/2021	29-Sep-21	Broker
FNO	c1	0.47	9/29/2021	29-Sep-21	Broker
Equity	ash1	7	9/29/2021	29-Sep-21	Broker
Equity	ab1	4	9/29/2021	29-Sep-21	Broker
Equity	te1	31.41	9/29/2021	29-Sep-21	Broker
FNO	s1	0.75	9/29/2021	29-Sep-21	Broker
Commodity	T1	19.45	9/30/2021	30-Sep-21	Broker
FNO	MF1	0.5	9/30/2021	30-Sep-21	Broker
FNO	B1	3.94	9/30/2021	30-Sep-21	Broker
FNO	c1	1.22	9/30/2021	30-Sep-21	Broker
FNO	U1	-0.75	9/30/2021	30-Sep-21	Broker
Equity	ash1	7.78	9/30/2021	30-Sep-21	Broker
Equity	ab1	3.99	9/30/2021	30-Sep-21	Broker
Equity	te1	31.41	9/30/2021	30-Sep-21	Broker
FNO	s1	0	9/30/2021	30-Sep-21	Broker
Commodity	T1	19.65	10/1/2021	1-Oct-21	Broker
FNO	MF1	0.51	10/1/2021	1-Oct-21	Broker
FNO	B1	3.98	10/1/2021	1-Oct-21	Broker
FNO	c1	1.2	10/1/2021	1-Oct-21	Broker
FNO	U1	-0.75	10/1/2021	1-Oct-21	Broker
Equity	ash1	7.82	10/1/2021	1-Oct-21	Broker
Equity	ab1	4	10/1/2021	1-Oct-21	Broker
Equity	te1	31.36	10/1/2021	1-Oct-21	Broker
Commodity	T1	19.69	10/4/2021	4-Oct-21	Broker
FNO	MF1	0.51	10/4/2021	4-Oct-21	Broker
FNO	B1	3.96	10/4/2021	4-Oct-21	Broker
FNO	c1	0.54	10/4/2021	4-Oct-21	Broker
FNO	U1	-3.01	10/4/2021	4-Oct-21	Broker
Equity	ash1	7.77	10/4/2021	4-Oct-21	Broker
Equity	ab1	1.99	10/4/2021	4-Oct-21	Broker
Equity	te1	34.39	10/4/2021	4-Oct-21	Broker
FNO	s1	3.04	10/4/2021	4-Oct-21	Broker
Commodity	T1	19.45	10/5/2021	5-Oct-21	Broker
FNO	MF1	0.51	10/5/2021	5-Oct-21	Broker
FNO	B1	3.98	10/5/2021	5-Oct-21	Broker
FNO	c1	0.72	10/5/2021	5-Oct-21	Broker
FNO	U1	-3.01	10/5/2021	5-Oct-21	Broker
Equity	ash1	7.71	10/5/2021	5-Oct-21	Broker
Equity	ab1	1.99	10/5/2021	5-Oct-21	Broker
Equity	te1	34.49	10/5/2021	5-Oct-21	Broker
FNO	s1	3.02	10/5/2021	5-Oct-21	Broker
Equity	ab1	0.040821	9/29/2021	29-Sep-21	Online
Equity	ab1	0.020821	10/1/2021	1-Oct-21	Online
Equity	ash1	0.075229	9/29/2021	29-Sep-21	Online
Equity	ash1	0.075229	10/1/2021	1-Oct-21	Online
Equity	te1	0.331673	9/29/2021	29-Sep-21	Online
Equity	te1	0.361673	10/1/2021	1-Oct-21	Online
Commodity	T1	0.199203	9/29/2021	29-Sep-21	Online
Commodity	T1	0.199203	10/1/2021	1-Oct-21	Online
FNO	B1	0.028357	9/29/2021	29-Sep-21	Online
FNO	B1	0.028357	10/1/2021	1-Oct-21	Online
FNO	MF1	0.00498	9/29/2021	29-Sep-21	Online
FNO	MF1	0.00498	10/1/2021	1-Oct-21	Online

I have date Slicer 
once i select any date it should display table like below

Requirement:- eg. if user select 3rd oct 2021 from the date slicer then it should display broker data of 3rd oct but online data with any lastest date i.e. 1st oct of value 

if user select 26th sep 2021 then it should display data of broker data but online data of lastest date means 22nd sept value

if user select 1st oct 2021 then it should display data of broker data of 1st oct but online data of lastest date means 30th sept value not selected date value for online

I have created 2 measures:-

Broker_New1 =
SUMX (
FILTER (
'Table (3)',
[Type] = "Broker"
&& 'Table (3)'[Date]= SELECTEDVALUE ( 'Table (3)'[Date] )
),
'Table (3)'[Value]
)

Online_new1 =
VAR a =
MAXX (
FILTER (
ALL ( 'Table (3)' ),
[Date] < SELECTEDVALUE ( 'Table (3)'[Date] )
&& [Type] = "Online"
),
[Date]
)
RETURN
SUMX (
FILTER (
ALL ( 'Table (3)' ),
[Date] = a
&& [Type] = "Online"
&& [category] = SELECTEDVALUE ( 'Table (3)'[category] )

&& [Sub Category] = SELECTEDVALUE ( 'Table (3)'[Sub Category] )
),
'Table (3)'[Value]
)

In some cases Sub Total showing Blank, i need a row subtotal of each category

Need your help @amitchandak  @Greg_Deckler 
Thanks,

[v-jingzhang · Accepted solution]

Hi @Anshenterprices 

Try this measure for online values.

Online_new1 = 
VAR a =
    MAXX (
        FILTER (
            ALL ( 'Table (3)' ),
            [Date] < SELECTEDVALUE ( 'Table (3)'[Date] )
                && [Type] = "Online"
        ),
        [Date]
    )
RETURN
    CALCULATE (
        SUM ( 'Table (3)'[Value] ),
        ALLEXCEPT ( 'Table (3)', 'Table (3)'[Category], 'Table (3)'[Sub Category] ),
        'Table (3)'[Type] = "Online",
        'Table (3)'[Date] = a
    )

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Anshenterprices]

@amitchandak Above measures is not working.

[amitchandak]

@Anshenterprices , Prefer to use independent date table in slicer and try like

measure =
var _1 calculate(maxX(filter(Table,Table[Date]<= selectedvalue(Date[Date])), Table[Date]), allexcept(Table, Table[Type]))
return
calculate(sum(Table[Value]), filter(Table, Table[Date] =_1))

or with a date from table try like
measure =
var _1 calculate(maxX(filter( allexcept(Table, Table[Type]), Table[Date]<= selectedvalue(Table[Date])), Table[Date]),)
return
calculate(sum(Table[Value]), filter( allexcept(Table, Table[Type]), Table[Date] =_1))