Month number

[clairetran0426]

Hi everyone. I need to do some odd time transformation for my DateDim table in Power BI.

My DateDim is custom table.
Week start date is on Saturday.
For the last week of the month, if it has dates fall in two month (like this case 12/28/2019->1/3/2020), the month number of every day in that week is the next month.

In this screenshot, Month number of 12/28, 12/29, 12/30, 12/31 is 1 not 12.

I understand the logic behind it, but cant replicate from the original excel file to Power BI.

1st photo: the desired Date table in PowerBI, I got everything except Correct Month Number.
2nd photo: the shot from orig excel File which you can see the logic.

[v-kelly-msft]

Hi @clairetran0426 ，

You need a calculated column as below:

Correct Month number = 
var a = MONTH('Table'[Week Start on Sat ])
Return
SWITCH(TRUE(), a in {1,3,5,7,8,10,12} && 'Table'[Date].[Day]>25 && 31-'Table'[Date].[Day]+'Table'[Column]<7,1,
a in {4,6,9,11} && 'Table'[Date].[Day]>24 && 30-'Table'[Date].[Day]+'Table'[Column]<7,1,
a =2 && MOD(YEAR('Table'[Week Start on Sat ]),4)=0 &&'Table'[Date].[Day]>23 && 29-'Table'[Date].[Day]+'Table'[Column]<7,1,
a=2 && MOD(YEAR('Table'[Week Start on Sat ]),4)<>0 &&'Table'[Date].[Day]>22 && 28-'Table'[Date].[Day]+'Table'[Column]<7,1,
'Table'[Month Number]
)

And you will see :

For the related .pbix file,pls click here.

 
Best Regards,
Kelly

[clairetran0426]

Hi @v-kelly-msft;

Your solution works but only for Dec. Is there a way to make it dynamic, work for all 12 months?

[v-kelly-msft]

Hi @clairetran0426 ，

You said "For the last week of the month, if it has dates fall in two month (like this case 12/28/2019->1/3/2020), the month number of every day in that week is the next month." so every last days for a month,if it starts and ends in 2 different months,it should be calculated in 1 ,right?

So here,if you put 2019/11/30 to the table,it works fine.

Best Regards,
Kelly

[clairetran0426]

@v-kelly-msft 

No. for Nov 30th 2019, the correct month is 12. It is not always 1 for correct Month.

I just firgured out the logic for month number for this project is 5/4/4

Jan has 5 weeks

Feb has 4 weeks

March has 4 weeks

April has 5 weeks

May has 4 weeks

June has 4 weeks

July has 5 weeks

August has 4 weeks

Sep has 4 weeks

Oct has 5 weeks

Nov has 4 weeks

Dec has 4 weeks

In addition to that rule, the start day of week is on Sat. So in the senerio below, the 1st week of January is start on Start 12/28/2019 -> 1/3/2019.

The pic show the logic:

So 

[clairetran0426 · Accepted solution]

I found the solution that I need from another Thread

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

I will close this topic

[aqil1995]

Hi,
How can i calculate previous month data?

for example i selected febuary data and i want to show feb data and january data.

min of tower value = month selected (Feb 2020)
Tower Previous = month before (jan 2020)

but i cant find a way to display the previous month data.

[MFelix]

Hi  @clairetran0426 ,:

On the query editor try the following code for a calculated column:

Date.Month(Date.StartOfWeek([Date],6) +#duration(7,0,0,0))

[clairetran0426]

@MFelix I didnt use query editor. It is a custome Date table