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....
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

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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)