Forum Discussion
YTD & Week over Week
Hi all,
I am trying to show Week over Week % changes & Fiscal YTD running total changes, week over week. However, instead of showing a table I am trying to show it in a Zebra BI card, as shown in the picture below because it consolidates both values into one & returns value/% difference all in one place.
I am simply taking a COUNT of "Start Date" which is the date a candidate starts working on a task. However, the fiscal year aspect of the calculation and them being measures makes it hard to filter/conceptualize for me as I normally do these types of calculations in a matrix/table. Here are my formulas below that have not worked:
(I believe both below were close to working but they are returning that week's values but for differnt years 2021 AND 2022)
ThisWeekStarts(TW) = CALCULATE([STARTCOUNT],'DATE'[WEEK OF YEAR] = WEEKNUM(TODAY(),1))
LASTWEEKSTARTS(LW) = CALCULATE([STARTCOUNT],'DATE'[WEEK OF YEAR] = WEEKNUM(TODAY(),1) -1)
(I think this solved my issue of fiscal YTD Starts but not sure and still doesn't solve for showing the growth percentage between this week's fiscal running total compared to last weeks)
FISCAL_RUNNINGTOTAL = CALCULATE[STARTCOUNT],DATESYTD('DATE'[DATE],"31/5"))
Any advice/suggestions help as I am trying to learn PowerBI and time intelligence is proving to be the most difficult part in that path.
Thank you
2 Replies
learning_dax , You YTD Week will be like
Year Week = [Year]*100 +[Week]
YTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] <= Max('Date'[Week]) ))
LYTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] <= Max('Date'[Week])))For FY ending on 31st May
try
FY = if(month([Date]) <=5, year([Date])-1, year([Date]) )
Year Start Date = if(month([Date]) <=5, date(year([Date])-1,6,1), date(year([Date]),6,1) )Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
min week start of year = minx(filter('Date',[FY] =earlier([FY])),[Week Start date])FY Week = datediff([min week start of year],[Week Start date], week)+1
Then measures
YTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[FY]=max('Date'[FY]) && 'Date'[FY Week] <= Max('Date'[FY Week]) ))
LYTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[FY]=max('Date'[FY])-1 && 'Date'[FY Week] <= Max('Date'[FY Week])))refer
Power BI for Beginners - Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWsPower 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=pnAesWxYgJ8Hi, learning_dax
Have you taken amitchandak advice? We can't find any errors from the information you provide, and it is difficult to give you substantial help.
Please add more details about your problem, I don't even know what's wrong with your results.
How to Get Your Question Answered Quickly - Microsoft Power BI Community
Janey
