Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
YunJ
Post Prodigy
Post Prodigy

How to write specific date column?

Hi,

 

I made a Date table as following. Capture1.PNG

When select a YearMonth such as 2020/05, the visual will show its YearWeeknum.

However, I also want it show these YearWeeknum belongs to which date.

Capture2.PNG

So I want to write a new column like the following: 

For example, when select YearMonth 202005, it should be 2020/5/1-2020/5/3 (which is YearWeeknum 202018 in the previous bar chart.), 2020/5/4-2020/5/10, 2020/5/11-2020/5/17........

Anyone know how to write this new column in power bi?

Capture3.PNG

 

Thanks a lot for your time!

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

Hi, @YunJ 

 

I'd like to suggest you create a calculated column as below. The pbix file is attached in the end.

DateDuration = 
var _min = 
CALCULATE(
    MIN('Date'[Date]),
    FILTER(
        ALL('Date'),
        'Date'[YearWeeknum]=EARLIER('Date'[YearWeeknum])&&
        'Date'[Month]=EARLIER('Date'[Month])
    )
)
var _max = 
CALCULATE(
    MAX('Date'[Date]),
    FILTER(
        ALL('Date'),
        'Date'[YearWeeknum]=EARLIER('Date'[YearWeeknum])&&
        'Date'[Month]=EARLIER('Date'[Month])
    )
)
return
_min&"-"&_max

 

Result:

a1.png

 

Best Regards

Allan

 

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

View solution in original post

9 REPLIES 9
v-alq-msft
Community Support
Community Support

Hi, @YunJ 

 

I'd like to suggest you create a calculated column as below. The pbix file is attached in the end.

DateDuration = 
var _min = 
CALCULATE(
    MIN('Date'[Date]),
    FILTER(
        ALL('Date'),
        'Date'[YearWeeknum]=EARLIER('Date'[YearWeeknum])&&
        'Date'[Month]=EARLIER('Date'[Month])
    )
)
var _max = 
CALCULATE(
    MAX('Date'[Date]),
    FILTER(
        ALL('Date'),
        'Date'[YearWeeknum]=EARLIER('Date'[YearWeeknum])&&
        'Date'[Month]=EARLIER('Date'[Month])
    )
)
return
_min&"-"&_max

 

Result:

a1.png

 

Best Regards

Allan

 

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

Hi @v-alq-msft 

Thanks for your help. I'm wondering can this sort in the correct order?

Capture.PNG

Hi, @YunJ 

 

I'd like to suggest you modify the calculated table as below.

Date = 
ADDCOLUMNS(
    ADDCOLUMNS(
        CALENDAR(DATE(2020,1,1),DATE(2020,12,31)),
        "Year",YEAR([Date]),
        "Quarter","Q"&FORMAT([Date],"Q"),
        "Month",FORMAT([Date],"MM"),
        "Day",FORMAT([Date],"DD"),
        "YearQuarter",FORMAT([Date],"YYYY")&"Q"&FORMAT([Date],"Q"),
        "YearMonth",FORMAT([Date],"YYYY/MM"),
        "Weekday",WEEKDAY([Date],2),
        "Weeknum",WEEKNUM([Date],2)
    ),
    "YearWeeknum",[Year]*100+[Weeknum],
    "YearMonthWeeknum",[Year]*100+[Month]*10+[Weeknum]
)

 

Then you may make 'DateDuration' column selected, click 'Sort by column' in 'Column tools', sort by 'YearMonthWeeknum'.

c1.png

 

Result:

c2.png

 

Best Regards

Allan

 

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

amitchandak
Super User
Super User

@YunJ , not very clear to me. Do need week start and end date

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1 //Monday start
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)

 

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1 //Sunday Start
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)

 

Week name = [Week Start date] & " to "& [Week End date]

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

Hi @YunJ ,

 

Please use the below one:

Measure = CALCULATE(MIN('Table'[Date]),ALL('Table'[Year],'Table'[Quarter],'Table'[Month],'Table'[Day],'Table'[YearQuarter],'Table'[YearMonth],'Table'[Weekday],'Table'[Weeknum],'Table'[Date])) & "-" & CALCULATE(MAX('Table'[Date]),ALL('Table'[Year],'Table'[Quarter],'Table'[Month],'Table'[Day],'Table'[YearQuarter],'Table'[YearMonth],'Table'[Weekday],'Table'[Weeknum],'Table'[Date]))

 

How to write specific date column.PNG

 

 Aiolos Zhao

harshnathani
Community Champion
Community Champion

Hi @YunJ ,

 

Create a Column

 

Column 2 =

var a = CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[Week Num]))
var b = CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[Week Num]))

RETURN

CONCATENATE( CONCATENATE(a," - "), b)
 
 
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
 
 

Hi @harshnathani 

Thanks for your help.

For example for 2020/5/3, because it belongs to May, so it should be 5/1/2020-5/3/2020. I want it return the start/end start for the month itself.

Capture.PNG

Anonymous
Not applicable

@YunJ 

 

You need a if-else for this:

Measure = IF(CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[YearWeeknum])) < STARTOFMONTH('Table'[Date]),STARTOFMONTH('Table'[Date]),CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[YearWeeknum])))
& "-" & 
IF(CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[YearWeeknum])) > ENDOFMONTH('Table'[Date]),ENDOFMONTH('Table'[Date]),CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[YearWeeknum])))

 

 

 

How to write specific date column 2.PNG

 

Aiolos Zhao

Hi @Anonymous 

 

I'm wondering can this be sort in the correct order?

 

Thanks a lot for your time!

 

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.