'Last week' measure returning blank for w/c 30th Dec

[Anonymous]

Hi there, 

I'm hoping somone might be able to help!

I'm working on some 'last week' measures, but it's returning blank for w/c 30th Dec... I think because of how the dates fall, it may be getting confused?  Is there a way to amend the measure? 

Below is my dummy file with measures for you to see, along with the table that shows the blank values

https://www.dropbox.com/s/8h5n8e789b5scdw/Example%20file.pbix?dl=0

Also, I would **love** some help figuring out the measure for 'eng rate for week before last'(2 weeks ago) and 'weekly average engagement YTD'

Thank you so much!

Sonia

[amitchandak]

This because that is week 53 and logic consider Last year Max week as 52.

To get rid. From the date remove 7 days and get Max date in Var.  and use Year and Week of that

[amitchandak]

Like

Last Calendar Week Engagement = 
var _maxdt = maxx(DateKey,dateadd(DateKey[Date],-7,DAY))
var _weeknum = WEEKNUM(_maxdt)
// if(max(DATA[WEEK NUM])=1,53,max(DATA[WEEK NUM]))
var _year = YEAR(_maxdt)
// if(max(DATA[WEEK NUM])=1,Max(DateKey[Year])-1,Max(DateKey[Year]))

//if(MAX(DATA[YEAR])=1,MAX(DateKey[Year])-1,MAX(DATA[YEAR]))
Return
CALCULATE(
    [Total Engagement],
    FILTER(DateKey,DateKey[Week #]=_weeknum && DateKey[Year] =_year)
)

[Anonymous]

Hi @amitchandak , 

Amazing! Thank you so much!

One other thing I would love your help with is a measure for 'ave eng rate week before last (2 weeks ago)' and 'average weekly total engagement FYTD (where new year starts Oct). 

Thank you again for all of your help!

Sonia

[amitchandak]

Below two example measures can give you data 7 days, 14 days before. Even if look by date. So if you group on week num of week start or end date. These two should be 1 week, 2week behind. 

Try and let me know if something similar works for you

7 days before = CALCULATE(sum(Sales[Sales Amount]),DATEADD('Date'[Date],-7,DAY))
14 days before = CALCULATE(sum(Sales[Sales Amount]),DATEADD('Date'[Date],-14,DAY))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

Hi @amitchandak , 

I think these functions work (thank you!). I'd like to display the numbers in a KPI card ( with last week as indicator and week before as 'goal'), without needing to manually select the new week each time.  I can see why it's not working, but am not sure how to fix!

https://www.dropbox.com/s/j2tosid21rc28v5/Example%20file.pbix?dl=0 

Is there any way to achieve this?

Thanks again for all of your help

Sonia

[amitchandak · Accepted solution]

I found that last week's engagement Rate is the same as avg rate. I created a new formula .

Please check ta link

https://www.dropbox.com/s/awlce5relamc17n/Example%20file%282%29.pbix?dl=0

[Anonymous]

Thank you, @amitchandak 

That makes sense. Seems ill just have to select a 'max date' for them to work correctly. 

One last thing I'd love your help with is a measure for 'total weekly engagement FYTD (where year starts Oct)'. We have a measure that sums total engagement for 'last' week already, it's just getting a weekly average across the FYTD that i'm struggling with. 

Thank you again!

[amitchandak]

The card needs a max date, else will take the calendar date. So we have restricted in case we use -7 days stuff. 

I will check