Forum Discussion
Helper IVDisplay max date value in table on type while selection of any date from slicer
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,
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.
3 Replies
- amitchandak
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)) 
Anshenterpricesamitchandak Above measures is not working.
v-jingzhangCommunity Support
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.
