Column chart with most recent two date values

[harshadrokade]

@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

[Anonymous]

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

[harshadrokade]

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.

[Anonymous · Accepted solution]

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

[harshadrokade]

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?

[Anonymous]

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

[harshadrokade]

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?

https://community.powerbi.com/t5/Desktop/Show-the-last-X-years-based-on-the-current-Selection-of-the...

[Ashish_Mathur]

Hi,

See this post - Solved: Re: Dax to get 2nd most recent value of selected d... - Microsoft Power BI Community

[harshadrokade]

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?