Filter issue

[Anonymous]

Hello Community   -   I have an issue with a filter that is wreaking havoc on my report.   Today a colleague asked me to filter a report by the field of the name of the person that entered the order.  

So, using the field "Taken By", I dragged this into the filter pane, and when I started selecting different names in the filter check box, this is the error I received (in the matrix table that was being filtered).     I have pinpointed the formula (below) that is causing the issue....I just do not know how to fix it.    

NOTE that I think it may have something to do with using the Order Date in the formula.   However, I have to use the Order Date (which is connected to my date table), otherwise the 2019 data gets all messed up.    The very bottom image is how it looks without any filter being applied.     Any help is appreciated!  

Order Count YTD LY =

VAR first_date = FIRSTDATE ( DATEADD ( 'Date Table'[Date], -12, MONTH ) )

VAR last_date = LASTDATE ( DATEADD ('Orders 2'[Order Date], -12, MONTH ) )

RETURN

    IF (

        ISBLANK ( first_date ) || ISBLANK ( last_date ),

        BLANK (),

        CALCULATE (

            DISTINCTCOUNT ( 'Orders 2'[Order] ),

            DATESBETWEEN ( 'Date Table'[Date], first_date, last_date )

        )

    )

[mahoneypat]

It is likely that when you filtered to a single person, that person didn't have orders on all dates and triggered that error in your last_date variable.  I would encourage you to calculate that variable w/o time intelligence functions (or use the Date table columns instead).

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

@mahoneypat   Hi Pat  -  Yes, I'm sure you are right.    Problem is if I try to use my date table column for the "last date", then the 2019 data does not show up, which I don't understand since my Order Date is connected to my date table.   

[Anonymous]

@mahoneypat   Actually, after further review, I think it is my YOY % formula causing the problem.    Still not quite sure how to fix other than just totaling up 2019 in one measure, and 2020 in another, and doing a YOY of the difference.   

Count YOY YTD % Variance = IF (

    MIN ( 'Date Table'[Date] )

        <= CALCULATE ( MAX ( 'Orders 2'[Order Date] ), ALL ( Orders ) ),DIVIDE([2020 Distinct Count of Orders]-[Order Count YTD LY],[Order Count YTD LY],0)+0)

[mahoneypat · Accepted solution]

Assuming that your Date table is marked as date table and covers your whole order date range, have you tried SAMEPERIODLASTYEAR instead?

NewMeasure = 

CALCULATE (

            DISTINCTCOUNT ( 'Orders 2'[Order] ), SAMEPERIODLASTYEAR('Date Table'[Date))

Also, why do you need the ISBLANK logic?

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

@mahoneypat    Thanks Pat....ultimately your feedback helped me change some formulas and got things working.   I will post my solution later tonight for everyone's future reference in case someone has a similar problem they are trying to solve.  

Cheers!