Assistance with query to count clients with revenue over 5m

[michellem0771]

Please help!

I have a report where I'm comparing current year sales in one table and prior year sales in a different table.

My issue is with the prior year table. The table is simply showing Client, Revenue (sales), % total and Rank. All fields are dynamic based on a TopN slider; this is all working fine in the table.

My revenue calculation is based on same period last year and is:

Revenue SPLY = CALCULATE([Revenue],SAMEPERIODLASTYEAR(Date[Date]))

To include that into my TopN filter, that calculation is:

TopN Revenue SPLY by Client = IF([Client Ranking PY] <= 'TopN[TopN Value], Revenue SPLY])

One issue is that I am trying to count the clients with revenue (sales) where it's greater than 5m.  I have 5 filters and they all work to update that amount except for the Date filter.  

Issues:

1. When all filters are clear, the "client sales > 5m" total is incorrect.  If I choose one of the filters except the date filter, the amount is correct, however, when I clear the filter, it's incorrect again.  This is the filter I am using to calculate that amount:     
   Prior Year Client Count Sales SPLY > $5m =

   COUNTROWS (

       FILTER (

           SUMMARIZE (

               CALCULATETABLE (

                   VALUES ( 'Invoice Activity (CLI)'[Client] ),

                   'DATE KPI'[Rolling 12 Months 2LY]=TRUE()

               ),

               'Invoice Activity (CLI)'[Client],

               "Metric", CALCULATE (

                   [Revenue Rolling 12 Months SPLY],

                   'DATE KPI'[Rolling 12 Months 2LY]=TRUE()

               )

           ),

           [Metric] > 5000000

       )

   )
2. when I choose a date range, shown below in the screenshot, any dates in this calendar year show that number as (blank); which I suspect is because of the SAMEPERIODLAST year formula.

Additionally:

1. The Clients count (shown on the top left side) is incorrect and the Avg Rev/Client (based on example, this should be $293+k) amount are incorrect as well. 

The Client count should be in the perior year range (same period last year and not change), but I cannot get this to work, I've tried with "RemoveFilters" and that doesn't work.  

Unfortunately, I cannot attach my .pbix fil because of the proprietary data.

Any assistance would be greatly appreciated.

Thank you very much.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...