Forum Discussion
Display recent max date and values in matrix table while selection of any date from date slicer
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,
4 Replies
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
Anonymous 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
Anonymous
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
