Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Latest months by default bar chart

I am trying to show the data for the past three months on the bar charts and I keep failing to do so. It will usually come up with data for all the months. What I expect is that I can show the sales of the latest 3 months when the slicer for month is not filtered, if not the bar chart will show the data of the months selected in the slicer. Is there any DAX or ways that could help me solving this? Appreciate any kind of advice or knowledge related.

1 ACCEPTED SOLUTION
sturlaws
Resident Rockstar
Resident Rockstar

Hi @Anonymous ,

According to you description, you want to have month as axis in a chart, as well as month in a slicer. In Power BI it is not possible to separate filter context coming from a chart/visualization from filter context coming from a slicer.

 

In order to do what you are trying to do, you need to keep a separate month table and use this table for slicer values. Then you can create this measure:

Measure =
IF (
    ISFILTERED ( Months[monthNo] );
    CALCULATE (
        SUM ( 'Table'[Sales] );
        FILTER ( 'Table'; 'Table'[monthNo] IN VALUES ( Months[monthNo] ) )
    );
    CALCULATE (
        SUM ( 'Table'[Sales] );
        FILTER ( 'Table'; 'Table'[monthNo] >= MONTH ( TODAY () ) - 2 )
    )
)

 

here is a demo

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

How to Get Your Question Answered Quickly 

View solution in original post

1 REPLY 1
sturlaws
Resident Rockstar
Resident Rockstar

Hi @Anonymous ,

According to you description, you want to have month as axis in a chart, as well as month in a slicer. In Power BI it is not possible to separate filter context coming from a chart/visualization from filter context coming from a slicer.

 

In order to do what you are trying to do, you need to keep a separate month table and use this table for slicer values. Then you can create this measure:

Measure =
IF (
    ISFILTERED ( Months[monthNo] );
    CALCULATE (
        SUM ( 'Table'[Sales] );
        FILTER ( 'Table'; 'Table'[monthNo] IN VALUES ( Months[monthNo] ) )
    );
    CALCULATE (
        SUM ( 'Table'[Sales] );
        FILTER ( 'Table'; 'Table'[monthNo] >= MONTH ( TODAY () ) - 2 )
    )
)

 

here is a demo

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

How to Get Your Question Answered Quickly 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.