Forum Discussion

YunJ's avatar
YunJ
Post Prodigy
6 years ago
Solved

How to write specific date column?

Hi,   I made a Date table as following.  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. ...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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:

     

    Best Regards

    Allan

     

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

YunJ's avatar
YunJ
Post Prodigy
6 years ago

Hi v-alq-msft 

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

v-alq-msft's avatar
v-alq-msft
Community Support
6 years ago

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

 

Result:

 

Best Regards

Allan

 

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