Local Month End

[SagarChauhan1]

Hi,  I have a different calender month ends. Eg: Jan and Feb for 28 days respectively and Mar for 35 days. So all non Qtr month is for 28 days and Quarter end month is for 35 days. How can i fix this in Power BI where they follow calender month end. Please help me to resolve this.

Sagar

[BA_Pete · Accepted solution]

Hi @SagarChauhan1 ,

It sounds like you need to create a 445 calendar. Try this thread:

https://community.powerbi.com/t5/Desktop/Custom-445-Fiscal-Year-Calendar/td-p/161257

Just be mindful that, by his own admission, Matt Allington's technique does not allow for the 53-week years. It also looks like he creates a 454 rather than a 445 calendar, but this should be easy enough to remedy by changing the order of his steps.

Pete

[amitchandak]

@SagarChauhan1 , Please find the attached calendar after the signature.

[SagarChauhan1]

Month Sr. Months Start Date : Closing Date : Days Weeks
1 January-2020 30-Dec-19 26-Jan-20 28 4Wk
2 February-2020 27-Jan-20 23-Feb-20 28 4Wk
3 March-2020 24-Feb-20 29-Mar-20 35 5wk
4 April-2020 30-Mar-20 26-Apr-20 28 4Wk
5 May-2020 27-Apr-20 24-May-20 28 4Wk
6 June-2020 25-May-20 28-Jun-20 35 5wk
7 July-2020 29-Jun-20 26-Jul-20 28 4Wk
8 August-2020 27-Jul-20 23-Aug-20 28 4Wk
9 September-2020 24-Aug-20 27-Sep-20 35 5wk
10 October-2020 28-Sep-20 25-Oct-20 28 4Wk
11 November-2020 26-Oct-20 29-Nov-20 35 5wk
12 December-2020 30-Nov-20 3-Jan-21 35 5wk

[V-lianl-msft · Accepted solution]

Hi @SagarChauhan1 ,

Refer to these:

https://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/ 

https://community.powerbi.com/t5/Desktop/Setup-a-custom-calendar-and-calculate-YTD-PTD-etc/td-p/1524... 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BA_Pete · Accepted solution]

Hi @SagarChauhan1 ,

It sounds like you need to create a 445 calendar. Try this thread:

https://community.powerbi.com/t5/Desktop/Custom-445-Fiscal-Year-Calendar/td-p/161257

Just be mindful that, by his own admission, Matt Allington's technique does not allow for the 53-week years. It also looks like he creates a 454 rather than a 445 calendar, but this should be easy enough to remedy by changing the order of his steps.

Pete

[amitchandak]

@SagarChauhan1 , You have to create custom calendar for that. can you provide the same in excel of table format.

[SagarChauhan1]

Hi,

The issue half reolved with this solution. but still my issue is 28 days no Qtr month so not sure how to convert that in formula. Below is the my Organazation month dates. If you can help with MTD and QTD formula.