Date Filter to Calculate a Ratio in 'Manage Power Pivot View'

[BenZed]

Hi guys,

The topic headline might not be the most accurate description of what I need, but I will explain in detail what I need in the following. I have a lot of experience with Excel, but I only recently started using Power Pivot. Therefore I am not 100% sure on whether the functionality that I need is available. I have already tried a variety of things, but so far I have not gotten it to work the way I need it to.

My goal is to calculate gross/net ratios of a dynamic historical time period which I can then multiply with each line. The important thing is that I need to do this on an order level, so I need the formula to work in the "Manage Power Pivot View" (What is the actual name for this anyways?)

To be more precise:

I have one column with gross revenues and one column with net revenues. I now want to sum all the the Net Revenues for the time period between 21 and 35 days ago and divide it by the gross revenues of the same time period. I tried to use the following formulas which all don't work for one reason or another:

CALCULATE(Sheet1[Net]/Sheet1[Gross],FILTERS(DATESBETWEEN(Sheet1[Created Date],LASTDATE(Sheet1[Created Date])-35,LASTDATE(Sheet1[Created Date])-22)))

CALCULATE(Sheet1[Net]/Sheet1[Gross],ALL(DATESBETWEEN(Sheet1[Created Date],LASTDATE(Sheet1[Created Date])-35,LASTDATE(Sheet1[Created Date])-22)))

Sheet1[Net] includes revenue numbers
Sheet1[Gross] includes revenue numbers
Sheet1[Created Date] contains dates of the last 35 days

Is it possible to do a sum of all values across the rows in this view?

Thanks in advance for your help!

 

[BenZed]

Nobody has advice for me on this? Or should I clarify my request more?