Creating measures that look at slicer selections

[ldavis4230]

Hello!

I'm attempting to create a report using measures so that the values will update dynamically based upon the user's slicer selections. I have four slicers that filter the data based on date:

Period 1 Start

Period 1 End

Period 2 Start

Period 2 End

I've been successful with several measures that filter on only one period, but am having trouble when trying to filter when either is true, meaning the date falls within Period 1 OR Period 2 (allowing for the possibility that they're not consecutive).

Here's my measure:

Product Count = CALCULATE(DISTINCTCOUNT('Product'[SKU]),

FILTER(Purchase,

DATESBETWEEN('Date of Purchase'[Purchase Date],SELECTEDVALUE('P1 Start'[1st Day of Month]),SELECTEDVALUE('P1 End'[Last Day of Month]))||

DATESBETWEEN('Date of Purchase'[Purchase Date],SELECTEDVALUE('P2 Start'[1st Day of Month]),SELECTEDVALUE('P2 End'[Last Day of Month]))

)

)

It seems to work okay until I attempt to add it into a table, when I get the below error.

Any thoughts on how to fix this, or an alternative solution?

Thanks so much!!

Lauren

[v-lid-msft · Accepted solution]

Hi @ldavis4230 ，

We can try to use the following measure to meet your requirement:

Product Count =
CALCULATE (
    DISTINCTCOUNT ( 'Product'[SKU] ),
    FILTER (
        Purchase,
        OR (
            AND (
                RELATED ( 'Date of Purchase'[Purchase Date] )
                    >= SELECTEDVALUE ( 'P1 Start'[1st Day of Month], BLANK () ),
                RELATED ( 'Date of Purchase'[Purchase Date] )
                    <= SELECTEDVALUE ( 'P1 End'[Last Day of Month], BLANK () )
            ),
            AND (
                RELATED ( 'Date of Purchase'[Purchase Date] )
                    >= SELECTEDVALUE ( 'P2 Start'[1st Day of Month], BLANK () ),
                RELATED ( 'Date of Purchase'[Purchase Date] )
                    <= SELECTEDVALUE ( 'P2 End'[Last Day of Month], BLANK () )
            )
        )
    )
)

If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

Best regards,

[ldavis4230]

Thank you so much @v-lid-msft ! 

[amitchandak]

Try like

Product Count = CALCULATE(DISTINCTCOUNT('Product'[SKU]),
FILTER(Purchase,
DATESBETWEEN('Date of Purchase'[Purchase Date],minx('P1 Start',SELECTEDVALUE('P1 Start'[1st Day of Month])),
maxx('P1 Start',SELECTEDVALUE('P1 End'[Last Day of Month])))||
DATESBETWEEN('Date of Purchase'[Purchase Date],minx('P2 Start',SELECTEDVALUE('P2 Start'[1st Day of Month]))
,maxx('P2 Start',SELECTEDVALUE('P2 End'[Last Day of Month]))
)))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin