Display max date value in table while selection of any date from 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

[v-janeyg-msft]

Hi, @Anshenterprices 

According to your description, I think you can create two measures and use it in table visual to display your desired result.

Like this:

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

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

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey

[Anshenterprices]

@v-janeyg-msft  Thank you so much .It's working. but once i use rowsubtotal in matrix table that time "online" row total is not appear. becase in the category there are sub-Category also in the data
Table looks like in below format
Category|SubCategory|Broker|Onlin|broker+Online|Borker-Online| 

[v-janeyg-msft]

@Anshenterprices  It is not recommended to use a matrix visual. Its context is more complicated. The measure is inherently dynamic and easy to change. 

If you need to use it , Please provide sample data and your desired result, I will modify the measure.

Best Regards,

Community Support Team _ Janey

[Anshenterprices]

sample data

Date	category	SubCategory	Value	Type
10/3/2021	Commodity	com1	10	Broker
10/3/2021	Commodity	Com2	40	Broker
10/3/2021	equity	Eq1	10	Broker
10/3/2021	equity	eq2	4	Broker
10/2/2021	Commodity	com1	10	Broker
10/2/2021	Commodity	com2	20	Broker
10/2/2021	equity	Eq1	10	Broker
10/2/2021	equity	Eq2	20	Broker
10/1/2021	Commodity	com1	5	Online
10/1/2021	Commodity	com2	15	Online
10/1/2021	equity	Eq1	10	Online
10/1/2021	equity	Eq2	20	Online
9/30/2021	Commodity	com1	20	Broker
9/30/2021	Commodity	com1	10	Online
9/30/2021	Commodity	com2	34	Broker
9/30/2021	Commodity	com2	45	Online
9/30/2021	equity	Eq1	56	Broker
9/30/2021	equity	Eq2	50	Online
9/30/2021	equity	Eq1	56	Broker
9/30/2021	equity	Eq2	50	Online
9/26/2021	Commodity	com1	30	Broker
9/26/2021	Commodity	com2	30	Broker
9/26/2021	equity	Eq1	20	Broker
9/26/2021	equity	Eq2	20	Broker
9/22/2021	Commodity	com1	14	Online
9/22/2021	Commodity	com2	5	Online
9/22/2021	equity	Eq1	12	Online
9/22/2021	equity	Eq2	12	Online

[Anshenterprices]

@v-janeyg-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

[v-janeyg-msft · Accepted solution]

Hi, @Anshenterprices 

I'm a bit busy today so I didn't have time to reply. You need to use summraize function to calculate total.

You can try:

Online = 
SUMX (
    SUMMARIZE (
        'Table 1',
        [category],
        [SubCategory],
        [Date],
        [Type],
        [Value],
        "online1",
            VAR a =
                MAXX (
                    FILTER (
                        ALL ( 'Table 1' ),
                        [Date] < SELECTEDVALUE ( 'Table 1'[Date] )
                            && [Type] = "Online"
                    ),
                    [Date]
                )
            RETURN
                SUMX (
                    FILTER (
                        ALL ( 'Table 1' ),
                        [Date] = a
                            && [Type] = "Online"
                            && [category] = SELECTEDVALUE ( 'Table 1'[category] )
                            && [SubCategory] = SELECTEDVALUE ( 'Table 1'[SubCategory] )
                    ),
                    [Value]
                )
    ),
    [online1]
)

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey