Forum Discussion

Post Partisan

1 year ago

Solved

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

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

Need Help

Anonymous

1 year ago

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.

Test File.pbix121 KB

7 Replies

amitchandak
Super User
1 year ago
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-Last-Week/ba-p/1051123
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-5c3243d1f9

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])))
Uzi2019
Community Champion
1 year ago
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!

Anonymous

Not applicable

1 year ago

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

Post Partisan

1 year ago

Hi Anonymous,

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

Thank you

Anonymous

Not applicable

1 year ago

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.

Test File.pbix121 KB

Forum Discussion

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

7 Replies

Related content

Sample unification

Month to Month Flow Calculation

AdventureWorks Sample

Publish error for Bicycles sample data

Power BI sample reports, refreshed with modern visual defaults