Display recent max date and values in matrix table while selection of any date from date slicer

[Anshenterprices]

Hi Team,

Source Data

Date	category	Value	Type
10/3/2021	Commodity	50	Broker
10/3/2021	equity	10	Broker
10/2/2021	equity	30	Broker
10/2/2021	Commodity	30	Broker
10/1/2021	Commodity	20	Online
10/1/2021	equity	30	Online
9/30/2021	Commodity	34	Broker
9/30/2021	equity	56	Broker
9/30/2021	Commodity	45	Online
9/30/2021	equity	50	Online
9/26/2021	equity	20	Broker
9/26/2021	Commodity	30	Broker
9/22/2021	equity	12	Online
9/22/2021	Commodity	14	Online

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

category	Broker	Online
Commodity	50	20
equity	10	30

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

Need your help @amitchandak  @Greg_Deckler 
Thanks,

[v-yiruan-msft]

Hi @Anshenterprices ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a date dimension table and apply the date field as slicer option

Date = CALENDAR ( MIN ( 'Table'[Date] ), MAX ( 'Table'[Date] ))

2. Create two measures as below to get the value from latest recent date

Measure = 
VAR _seldate =
    SELECTEDVALUE ( 'Date'[Date] )
VAR _recentdate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Date] <= _seldate
                && 'Table'[Type] = SELECTEDVALUE ( 'Table'[Type] )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[Date] = _recentdate )
    )

NValue = SUMX(VALUES('Table'[Type]),[Measure])

Best Regards

[Anshenterprices]

@v-yiruan-msft 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

[v-yiruan-msft]

Hi @Anshenterprices ，

I'm sorry I'm confused about your expected result. According to the data you provided, when you select 1st oct 2021, there is only online data for 1st oct 2021 as shown below and there is no data for 10.1 borker...

So, could you please share the results you are looking for with actual examples. For example, what kind of data should visual display when each date is selected? For example:

Selected date: 1st oct 2021

Expected result:

category	Broker	Online
Commodity	?	?
equity	?	?

Best Regards

[Anshenterprices]

@v-yiruan-msft 

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

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