topic Re: Split Month into Biweekly but Still Remain in Date Format in DAX Commands and Tips

Split Month into Biweekly but Still Remain in Date Format

Anonymous — Wed, 24 Aug 2022 15:30:20 GMT

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.

Re: Split Month into Biweekly but Still Remain in Date Format

Greg_Deckler — Wed, 24 Aug 2022 15:46:52 GMT

Anonymous 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.

Re: Split Month into Biweekly but Still Remain in Date Format

Anonymous — Wed, 24 Aug 2022 16:30:05 GMT

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

Re: Split Month into Biweekly but Still Remain in Date Format

Greg_Deckler — Thu, 25 Aug 2022 13:11:15 GMT

Anonymous 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.

Re: Split Month into Biweekly but Still Remain in Date Format

v-yadongf-msft — Mon, 29 Aug 2022 09:44:58 GMT

Hi Anonymous ,

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.