Forum Discussion
Column chart with most recent two date values
Ashish_Mathur Since you alredy created a pbix file on this, sharing this with you.
I have below data & I have slicers on Month, Student name & Subject name
I want a column chart created that will show values for selected date & most recent date after selected date.
E.g. If I selected Student as A, Subject as ABC2 & date as 1 Dec 2020 in the slicers, the column chart shoudl show the two values on column chart as below-
Date-1 Dec 2020 , Value-100
Date-1 Aug 2020, Value- 90
PLs also note that I also have some other visuals getting filtered based on Date slicer.
| Month-DD/MM/YYYY | Value | Student name | Subject name |
| 01/01/2020 | 100 | A | ABC1 |
| 01/03/2020 | 80 | A | ABC1 |
| 01/08/2020 | 90 | A | ABC1 |
| 01/12/2020 | 70 | A | ABC1 |
| 01/01/2020 | 70 | A | ABC2 |
| 01/03/2020 | 80 | A | ABC2 |
| 01/08/2020 | 90 | A | ABC2 |
| 01/12/2020 | 100 | A | ABC2 |
| 01/01/2020 | 10 | B | ABC1 |
| 01/03/2020 | 100 | B | ABC1 |
| 01/08/2020 | 70 | B | ABC1 |
| 01/12/2020 | 40 | B | ABC1 |
| 01/01/2020 | 30 | B | ABC2 |
| 01/03/2020 | 80 | B | ABC2 |
| 01/08/2020 | 30 | B | ABC2 |
| 01/12/2020 | 10 | B | ABC2 |
Hi harshadrokade ,
I updated the sample pbix file, please check whether it can return your expected result. You can find the details in the attachment.
1. Create a date dimension table(DO NOT create any relationship with your fact table)
Date = VALUES('Table'[Month-DD/MM/YYYY])2. Update the formula of measure [] as below
Most recent two date values = VAR _selstu = SELECTEDVALUE ( 'Table'[Student name] ) VAR _selsubject = SELECTEDVALUE ( 'Table'[Subject name] ) VAR _maxdate = SELECTEDVALUE ( 'Date'[Month-DD/MM/YYYY] ) VAR _secondmdate = CALCULATE ( MAX ( 'Table'[Month-DD/MM/YYYY] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Month-DD/MM/YYYY] < _maxdate ) ) RETURN CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( 'Table', 'Table'[Month-DD/MM/YYYY] IN { _maxdate, _secondmdate } ) )3. Apply the date field as slicer option to replace the field in fact table
Best Regards
8 Replies
Thanks Ashish_Mathur
Actually I want to create a column chart. I am unable to understand how to use these measures on the column chart. I think I need a filter that will filter my month axis by providing latest & 2nd most recent month. How can I achive that sir?
HI Greg_Deckler
Your post on below link helped me to filter the months based on other calendar table slicer. The only issue I am facing is that since the slicer value is selected from other delinked table, the other visuals that I have created from sales table are not getting filetered when I change the date. How can I manage this?
Hi harshadrokade ,
I created a sample pbix file(see attachment), please check whether that is what you want. You can create a measure as below:
Most recent two date values = VAR _selstu = SELECTEDVALUE ( 'Table'[Student name] ) VAR _selsubject = SELECTEDVALUE ( 'Table'[Subject name] ) VAR _maxdate = CALCULATE ( MAX ( 'Table'[Month-DD/MM/YYYY] ), REMOVEFILTERS ( 'Table'[Month-DD/MM/YYYY] ) ) VAR _secondmdate = CALCULATE ( MAX ( 'Table'[Month-DD/MM/YYYY] ), FILTER ( ALL ( 'Table' ), 'Table'[Student name] = _selstu && 'Table'[Subject name] = _selsubject && 'Table'[Month-DD/MM/YYYY] < _maxdate ) ) RETURN CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALL ( 'Table' ), 'Table'[Student name] = _selstu && 'Table'[Subject name] = _selsubject && 'Table'[Month-DD/MM/YYYY] IN { _maxdate, _secondmdate } ) )Best Regards
Thanks for this Anonymous . I want the column chart to show two columns on the chart, first the selected date & the second the 2nd recent date after selected date
Example, If I selected Dec 20 on slicer value & we have Latest date after Dec 20 as Oct 20, then the chart should show values as below-
If I selected Feb 21 on slicer value & we have Latest date after Feb 21 as Jan 21, then the chart should show values of Feb 21 & Jan 21.
Hi harshadrokade ,
I updated the sample pbix file, please check whether it can return your expected result. You can find the details in the attachment.
1. Create a date dimension table(DO NOT create any relationship with your fact table)
Date = VALUES('Table'[Month-DD/MM/YYYY])2. Update the formula of measure [] as below
Most recent two date values = VAR _selstu = SELECTEDVALUE ( 'Table'[Student name] ) VAR _selsubject = SELECTEDVALUE ( 'Table'[Subject name] ) VAR _maxdate = SELECTEDVALUE ( 'Date'[Month-DD/MM/YYYY] ) VAR _secondmdate = CALCULATE ( MAX ( 'Table'[Month-DD/MM/YYYY] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Month-DD/MM/YYYY] < _maxdate ) ) RETURN CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( 'Table', 'Table'[Month-DD/MM/YYYY] IN { _maxdate, _secondmdate } ) )3. Apply the date field as slicer option to replace the field in fact table
Best Regards
