Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Anshenterprices
Helper IV
Helper IV

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

Hi Team,

 

Source Data

DatecategoryValueType
10/3/2021Commodity50Broker
10/3/2021equity10Broker
10/2/2021equity30Broker
10/2/2021Commodity30Broker
10/1/2021Commodity20Online
10/1/2021equity30Online
9/30/2021Commodity34Broker
9/30/2021equity56Broker
9/30/2021Commodity45Online
9/30/2021equity50Online
9/26/2021equity20Broker
9/26/2021Commodity30Broker
9/22/2021equity12Online
9/22/2021Commodity14Online

 


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

categoryBrokerOnline
Commodity5020
equity1030
   

 


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



1 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]
)

vjaneygmsft_0-1634035002156.png

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

 

View solution in original post

6 REPLIES 6
v-janeyg-msft
Community Support
Community Support

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]
    )

vjaneygmsft_0-1633932633135.png

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

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

@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

sample data

DatecategorySubCategoryValueType
10/3/2021Commoditycom110Broker
10/3/2021CommodityCom240Broker
10/3/2021equityEq110Broker
10/3/2021equityeq24Broker
10/2/2021Commoditycom110Broker
10/2/2021Commoditycom220Broker
10/2/2021equityEq110Broker
10/2/2021equityEq220Broker
10/1/2021Commoditycom15Online
10/1/2021Commoditycom215Online
10/1/2021equityEq110Online
10/1/2021equityEq220Online
9/30/2021Commoditycom120Broker
9/30/2021Commoditycom110Online
9/30/2021Commoditycom234Broker
9/30/2021Commoditycom245Online
9/30/2021equityEq156Broker
9/30/2021equityEq250Online
9/30/2021equityEq156Broker
9/30/2021equityEq250Online
9/26/2021Commoditycom130Broker
9/26/2021Commoditycom230Broker
9/26/2021equityEq120Broker
9/26/2021equityEq220Broker
9/22/2021Commoditycom114Online
9/22/2021Commoditycom25Online
9/22/2021equityEq112Online
9/22/2021equityEq212Online



@v-janeyg-msft 

Sample data:-

 
CategorySub CategoryValueDateCDateType
CommodityT119.439/29/202129-Sep-21Broker
FNOMF10.59/29/202129-Sep-21Broker
FNOB13.959/29/202129-Sep-21Broker
FNOc10.479/29/202129-Sep-21Broker
Equityash179/29/202129-Sep-21Broker
Equityab149/29/202129-Sep-21Broker
Equityte131.419/29/202129-Sep-21Broker
FNOs10.759/29/202129-Sep-21Broker
CommodityT119.459/30/202130-Sep-21Broker
FNOMF10.59/30/202130-Sep-21Broker
FNOB13.949/30/202130-Sep-21Broker
FNOc11.229/30/202130-Sep-21Broker
FNOU1-0.759/30/202130-Sep-21Broker
Equityash17.789/30/202130-Sep-21Broker
Equityab13.999/30/202130-Sep-21Broker
Equityte131.419/30/202130-Sep-21Broker
FNOs109/30/202130-Sep-21Broker
CommodityT119.6510/1/20211-Oct-21Broker
FNOMF10.5110/1/20211-Oct-21Broker
FNOB13.9810/1/20211-Oct-21Broker
FNOc11.210/1/20211-Oct-21Broker
FNOU1-0.7510/1/20211-Oct-21Broker
Equityash17.8210/1/20211-Oct-21Broker
Equityab1410/1/20211-Oct-21Broker
Equityte131.3610/1/20211-Oct-21Broker
CommodityT119.6910/4/20214-Oct-21Broker
FNOMF10.5110/4/20214-Oct-21Broker
FNOB13.9610/4/20214-Oct-21Broker
FNOc10.5410/4/20214-Oct-21Broker
FNOU1-3.0110/4/20214-Oct-21Broker
Equityash17.7710/4/20214-Oct-21Broker
Equityab11.9910/4/20214-Oct-21Broker
Equityte134.3910/4/20214-Oct-21Broker
FNOs13.0410/4/20214-Oct-21Broker
CommodityT119.4510/5/20215-Oct-21Broker
FNOMF10.5110/5/20215-Oct-21Broker
FNOB13.9810/5/20215-Oct-21Broker
FNOc10.7210/5/20215-Oct-21Broker
FNOU1-3.0110/5/20215-Oct-21Broker
Equityash17.7110/5/20215-Oct-21Broker
Equityab11.9910/5/20215-Oct-21Broker
Equityte134.4910/5/20215-Oct-21Broker
FNOs13.0210/5/20215-Oct-21Broker
Equityab10.0408219/29/202129-Sep-21Online
Equityab10.02082110/1/20211-Oct-21Online
Equityash10.0752299/29/202129-Sep-21Online
Equityash10.07522910/1/20211-Oct-21Online
Equityte10.3316739/29/202129-Sep-21Online
Equityte10.36167310/1/20211-Oct-21Online
CommodityT10.1992039/29/202129-Sep-21Online
CommodityT10.19920310/1/20211-Oct-21Online
FNOB10.0283579/29/202129-Sep-21Online
FNOB10.02835710/1/20211-Oct-21Online
FNOMF10.004989/29/202129-Sep-21Online
FNOMF10.0049810/1/20211-Oct-21Online
 
 

 



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

Anshenterprices_0-1634010321789.png

 

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]
)

vjaneygmsft_0-1634035002156.png

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

 

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.