Hi Danielle,

There's no SAMEPERIODLASTWEEK equivalent, but this post has a solution.

The WEEKNUM function is explained here.

If you can't work with WEEKNUM, you might find DATEADDusing the 'day' interval useful. 

Hope that helps.

Mynda

Thanks, Mynda.

The link above to "this post" is the same link as the "WEEKNUM function is explained here".  I suspect this should not be the case? 

Could you please provide the correct link for "this post"?

Cheers, Danielle

Oops, sorry about that. Here is the correct link.

Thank-you.  This is a tricky one!

I have a few years of data, so added a year-week column to ensure each week is unique: 

Hi Danielle,

Please upload a sample Excel file with a small dataset that illustrates the scenarios and your measure in a PivotTable that returns no results. Also include a mockup of the results you'd expect to see in that PivotTable.

Mynda

Thanks, Mynda.

I've attached the pivot table and the desired outcome (to be used in Power BI).

Hi Danielle,

Thanks for the file. Please see file attached where I've focused on just one measure for the % Change v Prior Week Contacts Queued. 

Hope that points you in the right direction.

Mynda

Thanks, Mynda.

This works great with sum aggregates.  However, I'm struggling when the aggregate is average. 

The closest I got is with the below:

last14daysanswer = FORMAT((((CALCULATE(average('RAW Data by Time'[Average queue answer time]),DATESINPERIOD(DateTable[Date],max('RAW Data by Time'[Date]),-14,DAY)))-(CALCULATE(AVERAGE('RAW Data by Time'[Average queue answer time]),DATESINPERIOD(DateTable[Date],max('RAW Data by Time'[Date]),-7,DAY))))/86400),"nn:ss")

But doesn't provide the correct answer.

Are you able to please point me in the direction to work around this?

Many thank. 

Hi Danielle,

Why are you dividing it by 86400? The source data already contains the average queue answer time, then the measure averages those values. 

I'm not sure you need to divide it by any time component at all.

e.g. looking at the dates 20th May to 24th May the average of the average queue answer time for that period is 256.33. To convert that to hours & minutes, it's 4hours 16 minutes. i.e. 256.33/60

If I've misunderstood the objective, please provide an example and refer to the actual data in the file as I have done above so I can follow the logic.

Mynda

Thanks, Mynda.

Sorry, I was unclear.

This is what I did in Power BI:  divide by 86400 because this average answer time (raw data) is measured in seconds.  Average answer time actually shows how many seconds it took to answer the call (not what time the call was answered).  So I divide by 86400 and change format to nn:ss to show how many minutes/seconds it takes to answer the call.  Easier to interpret how many minutes&seconds (mm:ss) it took to answer the call, rather than show total seconds in the report.

But let me put the above aside.  The main thing I'm stuck trying to calculate is how many average seconds it took to answer the calls the previous week v. the latest week. 

Please see attached to see what I mean.

Hi Danielle,

Thanks for clarifying. When working with averages it doesn't make sense to subtract one period's values from another. 

In the file attached I've used the DATESBETWEEN function to calculate the period you want to average:

=CALCULATE(
     AVERAGE('RAWData'[Average queue answer time]),
     DATESBETWEEN(DateTable[Date],
     LASTDATE(DATEADD(DateTable[Date],-14,DAY)),  
     LASTDATE(DATEADD(DateTable[Date],-7,DAY)))
)/86400

You can probably use this for the other prior week calculations too.

Mynda

Thanks, Mynda.

This works great in Power Pivot for Excel.

But returns blank in Power BI.  Is there a different DAX format required for Power BI?

I used the below and the DAX measure doesn't return errors when I enter the new measure.  (note the table name is slightly different "RAW Data by Time" from the one used in excel "RAW Data". 

Actually, it seems to work using the below:

Last14answ =

EDIT: It seems to be blank when using in card.  Please see attached.  Is there a way to show this in a card?

It returns blank in a card because the card has no row context for the 'Date'. i.e. it doesn't know what the date is that it should be based on. 

In other words, the measure is referring to a date table, but the card has no date field present, so the long answer is no. You'd need to hard key the date in the measure or reference a variable in the measure for use in the card. Modeling tab > New Parameter.

Mynda