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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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)
 
 
YunJ's avatar
YunJ
Post Prodigy
6 years ago

Hi Anonymous 

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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

     

     

     

     

    Aiolos Zhao

    • YunJ's avatar
      YunJ
      Post Prodigy
      6 years ago

      Hi Anonymous 

       

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

       

      Thanks a lot for your time!