How to get custom month and week in the report?

[Anonymous]

Hi All,

I have a report where we are showing count of invoices per month in the report and the way we are calculating the month is basically month of posting date. However, I want my month to be calculated from last Tuesday of previous month to the last Tuesday of current month of posting date.

Similarly when calculating count of invoices per week, I want to calculate from Tuesday to Tuesday of posting date.

Does anyone has a clue how to do it?

Thanks in advance!

[Anonymous]

Thanks Amit!

I tried the solution provided by you and I am facing one more issue. The date in my table is basically a date hierarchy with year, quarter,month and day.  How do I use the queries provided in your blog in this case?

[v-jingzhang]

Hi @Anonymous 

You can add a Date column by combining these date hierarchy columns and change this new column to Date type. Then you can use Date column in Amit's codes. For example,

In Query Editor with M:

Add a custom column: = #date([Year], [Month], [Day])

Or In PBI Desktop with DAX:

Add a calculated column: DATE([Year],[Month],[Day])

Regards,
Community Support Team _ Jing Zhang
If this post helps, please Accept it as the solution to help other members find it.

[Anonymous]

Hi, 

Thanks!

I have already done for weeks now. Dop you know how to do it for the month?

Regards

Pranati

[v-jingzhang]

@Anonymous 

Assume you already have Weekday (Tue-Mon) column in Date table, you can try below codes to add custom month columns.

Month Start date = 
var _lastTuesdayThisMonth = MAXX(FILTER('Date','Date'[Weekday]=1 && 'Date'[Date]<=EOMONTH(EARLIER('Date'[Date]),0)),'Date'[Date])
return
IF('Date'[Date]<_lastTuesdayThisMonth,MAXX(FILTER('Date','Date'[Weekday]=1 && 'Date'[Date]<=EOMONTH(EARLIER('Date'[Date]),-1)),'Date'[Date]),_lastTuesdayThisMonth)

Month End date = 
var _lastTuesdayThisMonth = MAXX(FILTER('Date','Date'[Weekday]=1 && 'Date'[Date]<=EOMONTH(EARLIER('Date'[Date]),0)),'Date'[Date])
return
IF('Date'[Date]<_lastTuesdayThisMonth,_lastTuesdayThisMonth-1,MAXX(FILTER('Date','Date'[Weekday]=1 && 'Date'[Date]<=EOMONTH(EARLIER('Date'[Date]),1)),'Date'[Date])-1)

MonthDay = DATEDIFF([Month Start date],[Date],DAY)+1

Month of Year = 
var _lastTuesdayThisMonth = MAXX(FILTER('Date','Date'[Weekday]=1 && 'Date'[Date]<=EOMONTH(EARLIER('Date'[Date]),0)),'Date'[Date])
var _thisCalendarMonth = MONTH('Date'[Date])
return
IF(_thisCalendarMonth<12,IF('Date'[Date]<_lastTuesdayThisMonth,_thisCalendarMonth,_thisCalendarMonth+1),IF('Date'[Date]<_lastTuesdayThisMonth,_thisCalendarMonth,1))

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[Anonymous]

@v-jingzhang Your solution in working in a very weird way for Date 10th March 2021 it is giving month start date as 10th March 2021 and month end date as 9th March 2021. However, month start date should be 24th Feb 2021(Last wednesday of previous month of date) and month end date should be 30th March 2021 (Last tuesday of month of date).

Do you have a clue how to do it?

[Anonymous]

Hi,

I am trying the below query:

Month End date =
var _lastWednesdayThisMonth = MAXX(FILTER(ArrearBalanceDetail,ArrearBalanceDetail[Weekday(Wed-Tue)]=1 && ArrearBalanceDetail[PostingDate_Para]<=EOMONTH(EARLIER(ArrearBalanceDetail[PostingDate_Para]),0)),ArrearBalanceDetail[PostingDate_Para])
return
IF(ArrearBalanceDetail[PostingDate_Para]<_lastWednesdayThisMonth,_lastWednesdayThisMonth-1,MAXX(FILTER(ArrearBalanceDetail,ArrearBalanceDetail[Weekday(Wed-Tue)]=1 && ArrearBalanceDetail[PostingDate_Para]<=EOMONTH(EARLIER(ArrearBalanceDetail[PostingDate_Para]),1)),ArrearBalanceDetail[PostingDate_Para])-1)

I am facing one issue here. For current month it is giving me month end date as 9th March 2021 since it is the last tuesday till now. But it should be the last tuesday of this month which is 30th March 2021 even if there is no data for it.

[Anonymous]

Thanks Jing!

This is working. However, I want month of the year to also have year element, beacuse I have filters for the year in my report. Is that possible?

[v-jingzhang]

Not sure what is the format you want, I create below columns based on previous columns. 

Fiscal Year = YEAR('Date'[Month End date])

New Month of Year = 'Date'[Fiscal Year]*100+'Date'[Month of Year]

Jing

[amitchandak · Accepted solution]

@Anonymous , For week these two can help. Same need to be done for month

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-Power-BI-Turning/ba-p/1187482

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