Calendar last working day to next month

[Applicable88]

Hello everybody,

I want to set up a calendar table with a Revenue to my needs.The problem is that the last Working day already belongs to the next month. You can see it when the MonthYear number changes. It shows clearly that its the revenue month and not the calendar month.  I simplify it with only three columns here:

Date	MonthYear	Revenue
31.03.2021	202104	88888
01.04.2021	202104	34254657
02.04.2021	202104	36353
03.04.2021	202104	456456
04.04.2021	202104	345345
05.04.2021	202104	34534
06.04.2021	202104	34534
07.04.2021	202104	3453737
08.04.2021	202104	34254657

.....

30.04.2021

202105

8757

Date is Dateformat and MonthYear and Revenue is number format.

I want to have a table showing me each days revenue of the current month. Since the powerbi relative date filter is based on calendar month it would start with April 1st instead of March 31th. I also cannot use Min/Max of MonthYear since there are already entries for the next few years in it. I tried via measure but also with a duplicate of this table with changes. 

Here is a sample file:

https://drive.google.com/file/d/1Hh5IA1ziNW7p957bqupYitthz0ha6f6e/view?usp=sharing

Hope someone has a solution.

Thank you in advance.

[Jihwan_Kim · Accepted solution]

Hi, @Applicable88 

I am not sure whether I understood your question correctly.

If the customized YYYYMM column is needed, like same as the above, please check the below picture and I think it can be made in Power Query Editor.

1 Create a normal Year & Month column (number format like below picture).

2. Create a normal End of Month Column.

I think until here is not very difficult.

3. create a Custom Column: if Date is equal to End of Month, then year & month +1, else year & month.

4. It works unless the month is December. I think you can add one more condition and it will be solved.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Applicable88]

Hello @Jihwan_Kim , 

thank you very much. The only problem I left with is that the last workday will change depending on, if one of the days 29,30,31th, is on a weekend. But I think the solution still works pretty well for me for certain month. I would get a problem in July for example. The 31st is a Saturday, which means not the 31st belongs to the next day, but the 30th. Which again give me wrong revenue data for that month. I think the only way is to change manually some month in the calendar table.