Forum Discussion
Weekly/Monthly Calculations
Hello,
This is a 2-part question. I need to sum quantities by period. The data is such that weeks are defined by Period = Monday date and months are defined as Period = 1st of the month date.
My current query is set up as today-7 through today+14 (i.e. today's run brings in data for weeks of 4/27, 5/4, 5/11, 5/18 and months of 4/1, 5/1). I will be bringing in historical data, beginning week of 3/16, as a separate query and appending them. Each week, the oldest data will move to the historical query (i.e. next week, 4/27 data will move to historical).
My current measures are:
4 Replies
maj , Did not got it completely
You can get a Monday week like
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 Rank you can use to get this week, last week or next week or any week in past or rolling
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))Next Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])+1))
Last 12 week Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=min('Date'[Week Rank])-12 && 'Date'[Week Rank]<=max('Date'[Week Rank])))For month you can use
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(Table[Date])) last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd(Table[Date],-1,MONTH))) last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd(Table[Date],-1,MONTH)))) last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd(Table[Date],-12,MONTH))) last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd(Table[Date],-12,MONTH)))) Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd(Table[Date],-1,Month)) last QTR same Month (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd(Table[Date],-1,Qtr)))) MTD (Year End) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(Table[Date]))) MTD (Last Year End) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd(Table[Date],-12,MONTH),"8/31")))You can use filter of firstdate , last date or startofmonth etc to get the first day of month
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
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/Did you check the files I attached? The data is already defined by the periods (weeks or months). And I'm not sure RANK will work because each week the ranking gets bigger.
Example:
Today - current week is 0, last week is -1, week of 3/16 is - 7
Next Week - current week is 0, last week is -1, week of 3/16 is -8
Thanks,
-maj
maj , we always give incremental rank on week start or week in YYYYWW format.
max('Date'[Week Rank]) in the filter is this week, rest is +/- from here.
And this will change automatically every week
HI maj,
I'd like to suggest you take a look at the following blog to use date function manually define the filtered date range to calculate correspond records:
Time Intelligence "The Hard Way" (TITHW)Regards,
Xiaoxin Sheng
