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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anshenterprices
Helper IV
Helper IV

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

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

 


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

Anshenterprices_0-1634010321789.png

 



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

Anshenterprices_0-1634010321789.png


Need your help @amitchandak  @Greg_Deckler 
Thanks,





1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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
    )

21101401.jpg

 

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

View solution in original post

3 REPLIES 3
v-jingzhang
Community Support
Community Support

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
    )

21101401.jpg

 

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

Anshenterprices
Helper IV
Helper IV

@amitchandak Above measures is not working.

amitchandak
Super User
Super User

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors