Filter Matrix on Percent Change

[DataFunness]

Hello,

I am trying to take only records that have a percent change where the percent change is above 50% or below -50%. It isn’t comparing year to date sales to sales year to date last year. The ask is to come up with a ProRata sales value. If I am running for April, it would be 4 for April / 12 * all of last year sales.

Sales for all of last year = 15,000
April’s ProRata sales = 5,000 (4/12) * 15,000

I want to compare this year’s to date sales to the 5,000. If YTD sales is 3000, the percent cent to ProRata would be (3,000 – 5,000) / 5000 or 40%.
And for that %, I only want the records that are above 50% or below -50%.
Everything I do I get an out of memory processing error or circular dependency is detected.

[parry2k]

@DataFunness your model needs improvement but for now, you can do this, not sure why you have a summary calendar table, see attached:

[DataFunness]

Thank you @parry2k .

I think I'm getting close.  Two things:

• There are other filters that I needed, so this is how I added them (yellow highlight)
• But I'm not sure how to filter for the dates I need.  Eage page has its own calendar and the summary page had its own calendar.  Where can I add the calendar I need filtered:
  • 'Calendar Mkt Shr'[Year] = selectedYear
  • 'Calendar Mkt Shr'[Month] = selectedMonth

Thank you again,
Matt

[v-xingshen-msft]

Hi All,
Firstly  parry2k thank you for your solution!
And @DataFunness,For your question, you are trying to get the percentage you want based on last year's total sales, and this year's sales data from when you started until today, judging by the month selected, right?
For this question, here are some of our practical ideas we hope will help you!

ProRata_Sales_Percent_Change = 
VAR SelectedMonth = SELECTEDVALUE('Table'[Month])
VAR YearFraction = SelectedMonth / 12

-- Define last year's start and end dates
VAR LastYearStartDate = DATE(YEAR(TODAY()) - 1, 1, 1)
VAR LastYearEndDate = DATE(YEAR(TODAY()) - 1, 12, 31)

-- Calculate last year's total sales
VAR LastYearTotalSales = CALCULATE(
    SUM('Table'[Daily_Sales]),
    ALL('Table'),
    DATESBETWEEN('Table'[Date], LastYearStartDate, LastYearEndDate)
)

-- Define this year's start date and today's date
VAR CurrentYearStartDate = DATE(YEAR(TODAY()), 1, 1)
VAR CurrentDate = TODAY()

-- Calculate current year's year-to-date sales
VAR CurrentYearYTD = CALCULATE(
    SUM('Table'[Daily_Sales]),
    DATESBETWEEN('Table'[Date], CurrentYearStartDate, CurrentDate),
    REMOVEFILTERS('Table'[Month])
)

-- Calculate the ProRata percent change
RETURN
DIVIDE(CurrentYearYTD, LastYearTotalSales * YearFraction, 0)

Possibly our example data has limitations, there is no way to reproduce your negative number of cases, but the logic is unchanged, you can replace according to your actual situation, you want to filter their own data, you can filter the filter inside the filter, I hope that this idea will be helpful to you!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[DataFunness]

@v-xingshen-msft, Thank you for the reply.  The filter on the visiual (like you suggested) is how I had gotten the correct value (first image).  

The other half of my issue is I am trying to take that value and put it into this Summary table of different financial categories.  The $42 MM is the Market Share value to be displayed and should not be the $60 MM.  The problem is if I put all of the filters on the visual and not included in the measure, I get the $60 MM and not the correct filtered $ 42 MM.  Does that make sense?  I can get the report page that shows Market Summary correct (above image), the but this summary page is its own separate page.  

[v-xingshen-msft]

Hi @DataFunness ,
We try to understand what you mean, but still can't understand exactly what you need, can you show the result you want, or provide some sample pbix, so that we can better help you solve the problem!
Looking forward to your reply!

[parry2k]

@DataFunness It will be easier if you share pbix file using one drive/google drive with the expected output. Remove any sensitive information before sharing.

[DataFunness]

Hi @parry2k and @v-xingshen-msft ,

The overall report has many pages.  The Summary Page will display 1 measure from each page.  In this pbix example, I need the “Growth Increase or Decrease” total at the bottom of the Existing Customers page to go to the Summary page.  This is the one I’m having a problem with. 

The New Customer page is an example of how the total for New Customers is displayed on the Summary Page as expected.  

I created dummy data to match our data and part of our hierarchy. I may have switched between using  the names customer and dealer, but they are one in the same.  I added comments in the pages in the report.  I attached a CSV of the export of the Existing Customers page that shows how I the expected rows to be included for Existing Customers.  Please let me know what questions you have and thank you very much. 

Thank you so much

Link to files:  https://drive.google.com/drive/folders/13WGEsrVPOh9VqvIHZ-_Pwzlh3mu-M_5m?dmr=1&ec=wgc-drive-globalna...