cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fabo Frequent Visitor
Frequent Visitor

Show selected date and the dates before it

Hello everyone.

 

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

 

date_table.png          sales_table.png

 

model.png 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

 

visuals.png

 

How could I get this solved using DAX?

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: Show selected date and the dates before it

@fabo,

 

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
fabo Frequent Visitor
Frequent Visitor

Re: Show selected date and the dates before it

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.

 

Captura.PNG

 

 

The file is here.

 

Thank you for your support!

 

Best regards,

 

fabo

 

 

2 REPLIES 2
Highlighted
Community Support Team
Community Support Team

Re: Show selected date and the dates before it

@fabo,

 

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
fabo Frequent Visitor
Frequent Visitor

Re: Show selected date and the dates before it

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.

 

Captura.PNG

 

 

The file is here.

 

Thank you for your support!

 

Best regards,

 

fabo

 

 

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 267 members 3,130 guests
Please welcome our newest community members: