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
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.

View solution in original post

Highlighted
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

 

 

View solution in original post

2 REPLIES 2
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.

View solution in original post

Highlighted
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

 

 

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 53 members 839 guests
Please welcome our newest community members: