Help with card visuals for Week Over Week, Month over Month, and Year over Year (sample data)

[ERing]

I have data like below and need to generate the following values on a card visual.

1. Week over week Page_Views to Inbound_Calls conversion or the most recent week. So the card should show (Inbound_Calls/Page_Views) for the most recent week minus (Inbound_Calls/Page_Views) for the prior week.

2. Week over week Page_Views to Inbound_Calls conversion for the same week last year. So the card should show (Inbound_Calls/Page_Views) for the current week last year.

3. Month over Month Page_Views to Inbound_Calls conversion. The card should show MTD (Inbound_Calls/Page_Views). 

4. Month Over Month Page_Views to Inbound_Calls conversion for the same month last year. The card should show (Inbound_Calls/Page_Views) for the current month last year.

SAMPLE DATA LINK 

[v-zhangtin-msft]

Hi, @ERing 

It failed to open your link. Can you provide example data or sample files?

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ERing]

Hi @v-zhangtin-msft,

I have updated the link. You just have to download the PBIX report. Please try again.

Thank you

[v-zhangtin-msft · Accepted solution]

Hi, @ERing 

Please try the following methods. Now add these two columns to the date table.

Week = YEAR([Date])*100+WEEKNUM([Date],2)

Month = YEAR([Date])*100+MONTH([Date])

Inbound_Calls week = CALCULATE(SUM(Inbound_Calls[INBOUND_CALLS]),ALLEXCEPT('Calendar','Calendar'[Week]))

Page_Views week = CALCULATE(SUM(Web_Data[PAGE_VIEWS]),ALLEXCEPT('Calendar','Calendar'[Week]))

Week% = DIVIDE([Inbound_Calls week],[Page_Views week])

Week% previous = 
Var _prevwwek=CALCULATE(MAX('Calendar'[Week]),FILTER(ALL('Calendar'),[Week]<SELECTEDVALUE('Calendar'[Week])))
RETURN
CALCULATE([Week%],FILTER(ALL('Calendar'),[Week]=_prevwwek))

Measure = [Week%]-[Week% previous]

Result 1 = Var _Maxweek=MAXX(ALL('Calendar'),[Week])
RETURN
CALCULATE([Measure],FILTER(ALL('Calendar'),[Week]=_Maxweek))

Result 2 = 
Var _Maxweek=MAXX(ALL('Calendar'),[Week])
Var _lastyearweek=(LEFT(_Maxweek,4)-1)*100+RIGHT(_Maxweek,2)
RETURN
CALCULATE([Measure],FILTER(ALL('Calendar'),[Week]=_lastyearweek))

Here are the calculations for the monthly, which I put on Page 2:

Inbound_Calls month = CALCULATE(SUM(Inbound_Calls[INBOUND_CALLS]),ALLEXCEPT('Calendar','Calendar'[Month]))

Page_Views month = CALCULATE(SUM(Web_Data[PAGE_VIEWS]),ALLEXCEPT('Calendar','Calendar'[Month]))

Month% = DIVIDE([Inbound_Calls month],[Page_Views month])

Result 3 = Var _Maxmonth=MAXX(ALL('Calendar'),[Month])
RETURN
CALCULATE([Month%],FILTER(ALL('Calendar'),[Month]=_Maxmonth))

Result 4 = 
Var _Maxmonth=MAXX(ALL('Calendar'),[Month])
Var _lastyearmonth=(LEFT(_Maxmonth,4)-1)*100+RIGHT(_Maxmonth,2)
RETURN
CALCULATE([Month%],FILTER(ALL('Calendar'),[Month]=_lastyearmonth))

Is this the result you expected? Please review the attachment.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ERing]

@v-zhangtin-msft This worked. Thank you.

Can you also include how I can display a card visual for YTD and a card for Last Year?

Result 5 = Page_Views to Inbound_Calls conversion for Year to Date. The card should show (Inbound_Calls/Page_Views) for the Current Year to Date.

Result 6 = Page_Views to Inbound_Calls conversion for Last Year. The card should show (Inbound_Calls/Page_Views) for Last Year.

[v-zhangtin-msft]

Hi, @ERing 

You can try the following methods.

Result 5 = 
Var _currentsum1=CALCULATE(SUM(Inbound_Calls[INBOUND_CALLS]),FILTER(ALL('Calendar'),[Date].[Year]=YEAR(TODAY())))
Var _currentsum2=CALCULATE(SUM(Web_Data[PAGE_VIEWS]),FILTER(ALL('Calendar'),[Date].[Year]=YEAR(TODAY())))
RETURN
DIVIDE(_currentsum1,_currentsum2)

Result 6 = 
Var _lastyearsum1=CALCULATE(SUM(Inbound_Calls[INBOUND_CALLS]),FILTER(ALL('Calendar'),[Date].[Year]=YEAR(TODAY())-1))
Var _lastyearsum2=CALCULATE(SUM(Web_Data[PAGE_VIEWS]),FILTER(ALL('Calendar'),[Date].[Year]=YEAR(TODAY())-1))
RETURN
DIVIDE(_lastyearsum1,_lastyearsum2)

Please see the attached document.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Uzi2019]

Hi @ERing 

First try to create week column in calendar table:

Start Week =

STARTOFMONTH('Calendar'[Date]) + QUOTIENT(DATEDIFF(STARTOFMONTH('Calendar'[Date]),'Calendar'[Date],DAY),7)*7

End Week =

IF('Calendar'[Start Week]+6<=EOMONTH('Calendar'[Start Week],0),'Calendar'[Start Week]+6,EOMONTH('Calendar'[Start Week],0))

Week = FORMAT('Calendar'[Date],"MMM")& " | " &FORMAT('Calendar'[Start Week],"DD")& " - " & FORMAT('Calendar'[End Week],"DD")

Week No = WEEKNUM('Calendar'[Date],2)

---------------------------------------------------------------------------------------------------------------

Latest week calculation=

L_sales

var W= MAXX(ALLSELECTED('Calendar'),'Calendar'[Week No])

var L_week_sales=CALCULATE(SUM('table'[Spends(USD)]), 'Calendar'[Week No]= W)

RETURN L_week_sales

Prior week= 

var W= MAXX(ALLSELECTED('Calendar'),'Calendar'[Week No])

var P_week_sales=CALCULATE(SUM('table'[Spends(USD)]), 'Calendar'[Week No]= W-1)

RETURN P_week_sales

then calculate

prior week - latest week

add this to the card visual.

Same week last year= CALCULATE(sum('Table'[Spends]), FILTER(ALL('Date'),'Calendar'[Week no]=(max('Calendar'[Week no]) -53))) or -52

LY MTD Sales =
VAR CurrentDate = MAX('date'[Date])
VAR PreviousYearStart = DATE(YEAR(CurrentDate)-1, MONTH(CurrentDate), 1)
VAR PreviousYearEnd = EOMONTH(PreviousYearStart, 0)
RETURN
CALCULATE(
[sales amount],
FILTER(
ALL('date'),
'date'[Date] >= PreviousYearStart &&
'date'[Date] <= PreviousYearEnd &&
'date'[Date] <= EDATE(CurrentDate, -12)
)
)

 

I hope all above calculation help you to create your desired KPI card.

Instead of sum spends value use (Inbound_Calls/Page_Views).

I hope I answered your question!

[amitchandak]

@ERing , for MOM, QOQ and YOY you can use Time intellignece with Date/Calendar Table 

example

This month = CALCULATE([Net],DATESMTD(ENDOFMONTH('Date'[Date])))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))


last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
Previous year Month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth(dateadd('Date'[Date],-11,MONTH)))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last Year full = CALCULATE(SUM(Sales[Sales Amount]),previousyear('Date'[Date]))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

For WOW , you need additional columns in date table

Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

These measures can help
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
Last 8 weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
last two weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]<=max('Date'[Week Rank])-1
&& 'Date'[Week Rank]>=max('Date'[Week Rank])-3))

refer

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

Qtr Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER('Date'[Date])))

Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),PREVIOUSQUARTER(('Date'[Date])))