Average The Sum of Two Columns When Neither Are Blank Over 'x' Amount of Weeks

[Jlindsey00]

Hi everyone,

I have been working on this problem for over a week and keep getting stuck. 

Problem to Solve

- Divide the sum of SalesAmount by the sum of LeadQty for an AgentId in the last 4 Working Weeks. Essentially the four-week rolling average with some caveats.

Notes and Specifics

- I want to calculate only those dates (WeekEndDate) where both SalesAmount and LeadQty are not Blank.

- So a Working Week is a week where SalesAmount and/or LeadQty has a value

- One of the issues I'm running into is my measure counting a Non-Work Week as a Working Week.

- In the Sample Data below you'll see the calculation sums up the SalesAmount for each Working Week (highlighted) even if SalesAmount is Blank. This is because for that week LeadQty is not Blank. So we have 3 values for SalesAmount in those 4 Working Weeks. The same is true for Lead Qty, we are summing 3 values in the 4 Working Weeks.

DAX Measure

RollingAvg_4Week = 
VAR StartDate =
    FIRSTNONBLANK (
        'Calendar'[WeekEndDate],
        OR (
            SUM ( Sales[SalesAmount] ) > 0,
            SUM ( Leads[LeadQty] ) > 0
        )
    )
VAR EndDate =
    LASTNONBLANK (
        DATEADD ( 'Calendar'[WeekEndDate], -7, DAY ),
        OR (
            SUM ( Sales[SalesAmount] ) > 0,
            SUM ( Leads[LeadQty] ) > 0
        )
    )
RETURN
    CALCULATE (
        DIVIDE (
            SUM ( Sales[SalesAmount] ),
            SUM ( Leads[LeadQty] )
        ),
        DATESINPERIOD ( 'Calendar'[WeekEndDate], EndDate, -28, DAY )
    )

Sample Data in Excel

Power BI Model

- Agents Table w/ columns AgentId, Name

- Sales Table w/ columns AgentId, SalesAmount, SubmittedDate, WeekEndDate

- Lead Table w/ columns AgentId, LeadQty, WeekEndDate

- Calendar Table w/ columns Date, WeekEndDate

- Relationships: Calendar[Date] to Sales[SubmittedDate] and to Leads[WeekEndDate]

-- Agents[AgentId] to Sales[AgentId] and to Leads[AgentId]

Thank you all!

[Anonymous · Accepted solution]

Hi @Jlindsey00 ,

Please try:

Create a measure:

Measure = 
VAR CurrentDate = TODAY()
VAR StartDate = CurrentDate - 28
VAR EndDate = CurrentDate
VAR ValidSales = 
    CALCULATE(
        SUM('Table'[SaleAmount]),
        'Table'[SaleAmount] <> BLANK(),
        'Table'[LeadQty] <> BLANK(),
        FILTER(
            ALL('Date'),
            'Date'[Date] >= StartDate && 'Date'[Date] <= EndDate
        )
    )
VAR ValidLeads = 
    CALCULATE(
        SUM('Table'[LeadQty]),
        'Table'[SaleAmount] <> BLANK(),
        'Table'[LeadQty] <> BLANK(),
        FILTER(
            ALL('Date'),
            'Date'[Date] >= StartDate && 'Date'[Date] <= EndDate
        )
    )
RETURN
IF(ValidSales <> BLANK() && ValidLeads <> BLANK(), ValidSales / ValidLeads, BLANK())

The final page visual effect is as follows:

If you have any other questions please feel free to contact me.

The pbix file is attached.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Jlindsey00]

Thank you @Anonymous , however this is not exactly what I was aiming for. 

In you example, here is what should be added and averaged:

Dates 6/20, 6/6, 5/30 and 5/16 as they are the first four dates with either a SalesAmount or LeadQty.

So the sales amounts that should be summed are 1063 (6/20), 139 (5/30) and 437 (5/16).

And the lead quantities that should be summed are 18 (6/20), 20 (6/6) and 20 (5/30).

Given this information, how would you modify the measure?

Thank you