Split Month into Biweekly but Still Remain in Date Format

[ninimoke]

Help please!!! 

I have a Date column but I want to split the month into bi weekly with DAX(column or measure, not sure the best) but still remain as a date format. Example of what I want to achieve is attached. Thanks.

[ninimoke · Accepted solution]

Thanks @Greg_Deckler , I am still new to Power BI, if you can help with the DAX that can achieve that. Thanks.

[Greg_Deckler · Accepted solution]

@ninimoke Well, I don't know your data so not sure how you segment your months. If all you have is a Date column then perhaps something along the lines of:

Column =
  VAR __Date = [Date]
  VAR __StartOfMonth = DATE(YEAR(__Date), MONTH(__Date), 1)
  VAR __StartWeek = WEEKNUM(__StartOfMonth)
  VAR __CurrentWeek = WEEKNUM(__Date)
RETURN
  IF( (__CurrentWeek - __StartWeek) > 2,"Period 2", "Period 1")

Otherwise, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[v-yadongf-msft · Accepted solution]

Hi @ninimoke ,

As  @Greg_Deckler  said, please try following DAX to create new columns:

Column 1 = 
VAR __Date = [Date]
VAR __StartOfMonth = DATE(YEAR(__Date), MONTH(__Date), 1)
VAR __StartWeek = WEEKNUM(__StartOfMonth)
VAR __CurrentWeek = WEEKNUM(__Date)
RETURN
IF( (__CurrentWeek - __StartWeek) > 2,"P2", "P1")

Column 2 = FORMAT([Date],"mmm")

Please create a clustered column chart like below, you can split Month into Biweekly but Still Remain in Date Format:

If I misunderstand your demands, please provide some data screenshots (remember to hide sensitive information) to help us better solve your problem.

Best regards,

Yadong Fang

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

[ninimoke · Accepted solution]

Thanks @Greg_Deckler , I am still new to Power BI, if you can help with the DAX that can achieve that. Thanks.

[Greg_Deckler · Accepted solution]

@ninimoke Well, I don't know your data so not sure how you segment your months. If all you have is a Date column then perhaps something along the lines of:

Column =
  VAR __Date = [Date]
  VAR __StartOfMonth = DATE(YEAR(__Date), MONTH(__Date), 1)
  VAR __StartWeek = WEEKNUM(__StartOfMonth)
  VAR __CurrentWeek = WEEKNUM(__Date)
RETURN
  IF( (__CurrentWeek - __StartWeek) > 2,"Period 2", "Period 1")

Otherwise, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Greg_Deckler]

@ninimoke You should be able to create a column that flags the period and then just use that in your Legend. Should end up with 2 columns for each month.