Forum Discussion
Column chart with most recent two date values
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
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
Thanks Anonymous sir a lot sir for all you help.
This has worked sir but since I am not creating relationship bewteen date dimension table & fact table, my other visuals that are derived from fact table are not getting updated when I change the date which is derived from date dimention table.
How to manage to filter other visuals from Fact table sir with Date slicer?
Hi harshadrokade ,
If there are other visuals in the report and you need to dynamically display values based on the filtering of the date slicer, you may need to change the fields or the formulas for measures applied under the Values option in those visuals ( For example, add the condition: 'Facttable'[Date] between min('Datedimension'[ Date]) and max('Datedimension'[Date]) ). You can share a simplified pbix file with me and I will see if there is another more suitable solution later. Thank you.
Best Regards