WTD Same Days

[jagutierrezm]

Hello good afternoon, I am starting with PBI, I have a problem that I have not been able to solve, I will share it with you to see if you have a solution: I use the time dimension: https://www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/# They ask me to calculate the Profit Margin of a week against the previous week (put it in Spanish and add a few fields) Weeks start on Monday. They ask me to compare the same days of the week against the same days of the previous week Ex. If Today is 16-Sep (Wednesday) this would be the week from 14-Sep to 20Sep so when comparing the previous week I would do it until Wednesday (09-Sep)  This I have not been able to do If I add up to Round Wednesday it would be 800 from the previous week but if I put it on a card it brings Blank I have a measure Sales x Week without IVA $ = sum ('Ventas x Tienda'[Venta Precio s/IVA ($)]) And for the week before: Sales x Previous Week without IVA $ 3 = var Result = CALCULATE([Sales x Week without IVA $], FILTER(ALL(Fecha), Fecha[Calendario Desvio Semana] = SELECTEDVALUE(Fecha[Calendario Desvio Semana]) - 1 && Fecha[Numero Dia Semana] <= SELECTEDVALUE(Fecha[Numero Dia Semana]) ) ) return Result return Result Also add 2 Measures with Fixed values doing tests: Sales x Previous Week without IVA $ and Sales x Previous Week without IVA $ 2 Sure it is something simple but I have not found the solution.

[v-jayw-msft · Accepted solution]

Hi @jagutierrezm ,

It seems that you need to create a weeknum column in your table. Then you can get today's weeknum and calculate the sum of values in same week and sum of values in previous week.

Something like below.

weeknum = WEEKNUM('Table'[date],2)

this week = CALCULATE(SUM('Table'[value]),FILTER('Table','Table'[weeknum]=WEEKNUM(TODAY(),2)))

last week = CALCULATE(SUM('Table'[value]),FILTER('Table','Table'[weeknum]=WEEKNUM(TODAY(),2)-1))

If i misunderstand your meaning, please let me know.

Best Regards,

Jay

[amitchandak]

@jagutierrezm , Please format the post better. Every thing is in one line.

You can get week till dates like

WTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[Weekday] <=max('Date'[Weekday])))
LWTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -1) && 'Date'[Weekday] <=max('Date'[Weekday])))

This column you need in your date table for Monday week

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)

refer Power BI — 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...

Any Weekday Week - Start From Any day of Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...