Calculate KPI of last 7days vs the 7days prior

[Ronniemaccagee]

Hi, hope someone can help!

I want to show a KPI visual of:

The sum of rows between today and 7days ago VS the sum of rows between 7 and 14 days ago

To compare total amount of jobs completed last week vs the week before

Any ideas?

[v-kelly-msft · Accepted solution]

Hi @Ronniemaccagee ,

"The sum of rows between today and 7days ago VS the sum of rows between 7 and 14 days ago",you can create 2 measures as below：

sum of rows between today and 7days ago = CALCULATE(SUM('Table'[Sale]),FILTER(ALL('Table'),'Table'[Date]>=TODAY()-7&&'Table'[Date]<=TODAY()))

the sum of rows between 7 and 14 days ago = CALCULATE(SUM('Table'[Sale]),FILTER(ALL('Table'),'Table'[Date]<=TODAY()-7&&'Table'[Date]>=TODAY()-14))

And you will see:

 As for" To compare total amount of jobs completed last week vs the week before",could you pls upload some sample data with expected output and share the link with me?

Much appreciated.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@Ronniemaccagee , Have a calendar with the following columns

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Number = WEEKNUM([Date],2)
Week = if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number])
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
Week name = [Week Start date] & " to "& [Week End date]
Weekday = WEEKDAY([Date],2)
WeekDay Name = FORMAT([Date],"ddd")

This week vs last Week

This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

rolling

Rolling 7 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD(Table,today()-7,7,day))
Rolling 7 to 14 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD(Table,today()-14,7,day))

or
Rolling 7 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD(Table,max(Sales[Sales Date])-7,7,day))
Rolling 7 to 14 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD(Table,max(Sales[Sales Date])-14,7,day))

replace max date with slicer date to get a rolling from slicer date

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

My Video's

https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...

https://www.youtube.com/watch?v=7Jc3D4iaTqs
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/