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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
EWBWEBB
Helper II
Helper II

Slicer affecting measure

Hi

 

I am trying to count the total number of people that joined the business in the last two years.

 

I want to be able to use a slicer to select the current reporting period and then return all the new joiners from the 1st of January from 2 years before the last date of the selected period. 

 

So selecting any period that ends in 2023 would return all the joiners since 01/01/2021, a period ending in 2024 will return all joiners since 01/01/2022 and so on.

 

 

TotalNotMaleJoiners (Heads) L2Y = 
    CALCULATE(
        [TotalJoiners (Heads)]
        ,FILTER(
            'PersonDim',
            'PersonDim'[Gender] <> "Male"
        )
        ,FILTER(
            'DateDim',
            'DateDim'[FullDate] IN

            VAR SelectedYear = 
            YEAR(
                MAXX(
                    ALL('DateDim'),
                    SELECTEDVALUE('DateDim'[FullDate], TODAY()
                    )
                )
            )

            VAR Jan1TwoYearsAgo = 
            DATE(SelectedYear - 2,1,1)

            VAR PeriodMaxDate =             
            MAXX(
                ALL('DateDim'),
                SELECTEDVALUE('DateDim'[FullDate], TODAY()
                )
            )

            RETURN
            DATESBETWEEN('DateDim'[FullDate],Jan1TwoYearsAgo,PeriodMaxDate)
        )
    )

 

 

When I use a slicer to select a period it is overwriding all the date min/max, I'm assuming I need to wrap something with an ALL/ALLSELECTED but I've tried it all over the place and whatever I do the slicer seems to affect the measure.

Any help greatly appreciated.

 

My slicer is from a column in the DateDim table but is a text value so I can't just use MAX or MAXX on that.

 

 

 

1 ACCEPTED SOLUTION
EWBWEBB
Helper II
Helper II

Hi All

Took a different approach and got the desired result - also alot simpler.

 

TotalNotMaleJoiners (Heads) L2Y = 
VAR MaxDate = 
MAX('DateDim'[FullDate])

VAR CurrentYear =
   YEAR(
    MAX('DateDim'[FullDate])
   )

VAR Jan12YearsPrior = DATE(CurrentYear - 2,1,1)
    

VAR Joiners = 
CALCULATE(
    [TotalNotMaleJoiners (Heads)],
    FILTER(
        ALL('DateDim'), 'DateDim'[FullDate] >= Jan12YearsPrior &&
         'DateDim'[FullDate] <= MaxDate)
)

RETURN
Joiners

 

 

View solution in original post

6 REPLIES 6
sjoerdvn
Super User
Super User

something like

TotalNotMaleJoiners (Heads) L2Y = 
    VAR PeriodMaxDate = MAX('DateDim'[FullDate])
    VAR Jan1TwoYearsAgo = DATE(YEAR(PeriodMaxDate)  - 2,1,1)
    VAR  VAR DateRange = CALCULATE(DATESBETWEEN('DateDim'[FullDate],Jan1TwoYearsAgo,PeriodMaxDate), ALL('DateDim'))
    RETRN CALCULATE(
        [TotalJoiners (Heads)]
        ,'PersonDim'[Gender] <> "Male"
        ,ALL('DateDim')
        ,'DateDim'[FullDate] IN DateRange
    )
EWBWEBB
Helper II
Helper II

Hi All

Took a different approach and got the desired result - also alot simpler.

 

TotalNotMaleJoiners (Heads) L2Y = 
VAR MaxDate = 
MAX('DateDim'[FullDate])

VAR CurrentYear =
   YEAR(
    MAX('DateDim'[FullDate])
   )

VAR Jan12YearsPrior = DATE(CurrentYear - 2,1,1)
    

VAR Joiners = 
CALCULATE(
    [TotalNotMaleJoiners (Heads)],
    FILTER(
        ALL('DateDim'), 'DateDim'[FullDate] >= Jan12YearsPrior &&
         'DateDim'[FullDate] <= MaxDate)
)

RETURN
Joiners

 

 

Anonymous
Not applicable

I think

 VAR SelectedYear = 
 YEAR(  MAX(  'DateDim'[Fulldate] ) )

should work just fine to start with. 
By using the all function for your date table you remove it's filters, but that's not what you want in this case?

Good luck!

Hey

 

That did work, thank you. 

 

However when Apply the slicer to select the relevant period it seems to filter the measure.

The period slicer relates to about 20 dates, so I want to take the max of those days for the end date in the dates between. But I think what it is doing is over-riding the start date?

EWBWEBB
Helper II
Helper II

@ChiragGarg2512 - thanks for the suggestion.

Just gave it a go and if I do that the value in the visual doesn't change at all? 

 

Seems to be all or nothing?

I think what I want to do is wrap the DATESBETWEEN DateDim[FullDate] within an ALL but it doesn't like that.

ChiragGarg2512
Super User
Super User

@EWBWEBB , Breaking the interactions of the slicer with the visuals that contain the measure can help the case. 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors