Forum Discussion

harshadrokade's avatar
harshadrokade
Post Partisan
4 years ago
Solved

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 ...
  • Anonymous's avatar
    Anonymous
    4 years ago

    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