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
amhiggins
Frequent Visitor

Single Date Slicer to Apply Different Date Ranges to Different Plots

Hello!

 

I haven't quite be able to find a solution that fits my need, if there is one already please point me to it and apologies for the duplication!

 

I have a dashboard with two plots - one shows a number of new things created in a month and another shows a number of things that were created anytime before the same month that are still open (ongoing things).  I'd like to allow the user to select a month and update both plots.

 

As an example, take the image below, I have April selected.

The left plot shows new things, Date filter = [4/1/2021 - 4/30/2021]

The right plot shows ongoing things, Date filter = before 4/1/2021

 

Is there a way to do this with a single month slicer exposed to the user as shown below? 

 

amhiggins_0-1619715429342.png

 

EDIT: I want to add some context. I mocked up some data to show what I'm trying to do and where I'm at. The first set of pie charts (on the left) are what I want to see, I created these by manually filtering the visuals. The second set of pie charts are both using the Date slicer in the middle. So when I filter for 2021-04 the top chart is correct. For the bottom chart, the total count is correct but the categories don't work. The measure I'm using the for the bottom right chart is as follows:

Measure = 
var _min = MINX(ALLSELECTED('Table'), 'Table'[Date])
RETURN
CALCULATE(DISTINCTCOUNT('Table'[ID]), FILTER(ALL('Table'), 'Table'[Date] < _min))

amhiggins_0-1620243985560.png

 

1 ACCEPTED SOLUTION
v-xiaotang
Community Support
Community Support

Hi @amhiggins 

Not clear how your data model and table structure look like, so I create a sample:

 

v-xiaotang_0-1620215909972.png

You may take steps for reference:

Create the measure:

secondmeasure =
VAR _max =
    SELECTEDVALUE ( 'Table'[orderdate] )
RETURN
    CALCULATE (
        SUM ( 'Table'[salestotal] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[orderdate].[Year] = YEAR ( _max )
                && 'Table'[orderdate] >= 1
                && 'Table'[orderdate] <= _max
        )
    )

Result:

When you select 2020.2, secondmeasure counts total during 2020.1-2020.2

v-xiaotang_2-1620215910030.png

When you select 2021.05, secondmeasure counts total during 2021.1-2021.5

 

v-xiaotang_4-1620215910033.png

 

Hope it helps.

 

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-xiaotang
Community Support
Community Support

Hi @amhiggins 

Not clear how your data model and table structure look like, so I create a sample:

 

v-xiaotang_0-1620215909972.png

You may take steps for reference:

Create the measure:

secondmeasure =
VAR _max =
    SELECTEDVALUE ( 'Table'[orderdate] )
RETURN
    CALCULATE (
        SUM ( 'Table'[salestotal] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[orderdate].[Year] = YEAR ( _max )
                && 'Table'[orderdate] >= 1
                && 'Table'[orderdate] <= _max
        )
    )

Result:

When you select 2020.2, secondmeasure counts total during 2020.1-2020.2

v-xiaotang_2-1620215910030.png

When you select 2021.05, secondmeasure counts total during 2021.1-2021.5

 

v-xiaotang_4-1620215910033.png

 

Hope it helps.

 

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@amhiggins , Assume if date is joined to the table, you are displaying a measure that will filter on selected dates

 

 

second one you can use this measure

new measure =
var _max = maxx(allselected('Date'), 'Date'[Date])
var _min = minx(allselected('Date'), 'Date'[Date])
return
calculate([Measure],filter('Date', 'Date'[Date] =_min))

I was able to use a form of this measure to get an accurate total but it doesn't work with the colors on the donut chart. It sets each of the colors to 25%.

 

For reference, this is the measure I implemented to get the total:

New measure = 
var _max = MAXX(ALLSELECTED(table), table[Date].[Date])
var _min = MINX(ALLSELECTED(table), table[Date].[Date])
return
CALCULATE(DISTINCTCOUNT(table[id]), FILTER(ALL(table), table[Date].[Date] < _min))

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.