Show selected date and the dates before it

[fabo]

Hello everyone.

I’m trying to solve this problem. I have a Date Table and a Sales Table, both related by a key.

          

 

I need a line chart that shows data from January (the earliest date) to the selected date in a slicer.

The closest I got was deactivating interactions between the slicer and the line chart, but obviously the latter doesn’t respond to the selected date in the slicer.  If I keep the interaction, it just shows a dot.

How could I get this solved using DAX?

[v-chuncz-msft · Accepted solution]

@fabo,

You may try adding CROSSFILTER Function in an appropriate measure and set cross-filter direction to None.

[v-chuncz-msft · Accepted solution]

@fabo,

You may try adding CROSSFILTER Function in an appropriate measure and set cross-filter direction to None.

[fabo · Accepted solution]

Hi @v-chuncz-msft,

You gave me a nice idea.  I don't know if it's the more efficient idea but it works for me.

I made a copy of the Date Table just for being used in the line chart:

Date for visual = 'Date'

Then I made a measure to capture the current selection of the original Date Table:

Selection = CALCULATE(SUM('Date'[Date]))

I used that selection as a part of an argument to evaluate if the new dates are older or equal to it.  In the CALCULATE function I deactivated the cross filter between original dates and fact table dates with the function CROSSFILTER:

Sales by Month = 
    CALCULATE(
        SUM(Sales[Sales]);
        CROSSFILTER(
            'Date'[date_key];
            Sales[date_key];
            None
        );
        FILTER(
            'Date for visual';
            'Date for visual'[Date] <= [Selection]
        )
    )

Finally I added the new date table in the visual field, and I works just fine.

The file is here.

Thank you for your support!

Best regards,

fabo

[Anonymous]

 I have a similar problem to this but I am using a data table to display all values, and is not working for me. Could you please help?

https://community.powerbi.com/t5/Desktop/Data-table-to-display-preceding-data-based-on-date-slicer/m...