Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter issue

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 )
        )
    )
 
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    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

5 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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.   

      • mahoneypat's avatar
        mahoneypat
        Microsoft Employee
        6 years ago

        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