Weekly/Monthly Calculations

[maj]

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:

Last Week Quantity = CALCULATE(SUMX(FILTER('Report Connection', 'Report Connection'[PERIOD] = MIN('Report Connection'[PERIOD])+42), 'Report Connection'[Quantity]), 'Report Connection'[Time Bucket Profile] = "Weekly")

Current Week Quantity = CALCULATE(SUMX(FILTER('Report Connection', 'Report Connection'[PERIOD] = MIN('Report Connection'[PERIOD])+49), 'Report Connection'[Quantity]), 'Report Connection'[Time Bucket Profile] = "Weekly")

Next Week Quantity = CALCULATE(SUMX(FILTER('Report Connection', 'Report Connection'[PERIOD] = MIN('Report Connection'[PERIOD])+56), 'Report Connection'[Quantity]), 'Report Connection'[Time Bucket Profile] = "Weekly")

Is there a better way to manage this so that each week I don't have to manually update and/or create new measures?  It's currently based off the MIN date in the data, but since new data will be added each week, the MIN+xx will change each week.

As for the monthly periods, given the query structure (today-7 through today+14) sometimes the data will include last month and current month, sometimes it will include current month only, sometimes it will include current month and next month.  I will not add monthly data to the historical query.  How do I create a measure(s) to accomodate last month (if it exists in data), current month (will always exist in data), next month (if it exists in data).?

Below are links to sample data and a sample PBIX file.

https://drive.google.com/open?id=13CrcJ-Y_xAZ8ZrPIuulQ1tnBO_auQg18

https://drive.google.com/open?id=17YBBgkWWQMytpA3QGwpJqH5A0VwAqX81

I would appreciate any help on this.  Thanks in advance!

-maj

[Anonymous]

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

[amitchandak]

@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/

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

[maj]

@amitchandak 

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

[amitchandak]

@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