Forum Discussion

clairetran0426's avatar
clairetran0426
Frequent Visitor
6 years ago
Solved

Month number

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.

 
 

Need Help

8 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    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))

     

     

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      clairetran0426
      Frequent Visitor
      6 years ago

      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's avatar
        v-kelly-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        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