Forum Discussion

Josearce's avatar
Josearce
Regular Visitor
7 years ago
Solved

Calendar

Can you do one of calendars and how to put from/to week. Example: I have a list of dates from 8/27 to 9/2 and on another column I need that for those dates says 8/27 - 9/2, can that be done?
Need Help
Tutorial Requests
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Josearce

    Create three calculated columns as below

    year = YEAR([date])

weeknum = WEEKNUM([date],2)


date custom =
CONCATENATE (
    CONCATENATE (
        FORMAT (
            CALCULATE (
                MIN ( [date] ),
                ALLEXCEPT ( Sheet8, Sheet8[year], Sheet8[weeknum] )
            ),
            "m/dd"
        ),
        "-"
    ),
    FORMAT (
        CALCULATE (
            MAX ( [date] ),
            ALLEXCEPT ( Sheet8, Sheet8[year], Sheet8[weeknum] )
        ),
        "m/dd"
    )
)

     

     

    Best Reagrds

    Maggie

2 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Josearce

    Create three calculated columns as below

    year = YEAR([date])

weeknum = WEEKNUM([date],2)


date custom =
CONCATENATE (
    CONCATENATE (
        FORMAT (
            CALCULATE (
                MIN ( [date] ),
                ALLEXCEPT ( Sheet8, Sheet8[year], Sheet8[weeknum] )
            ),
            "m/dd"
        ),
        "-"
    ),
    FORMAT (
        CALCULATE (
            MAX ( [date] ),
            ALLEXCEPT ( Sheet8, Sheet8[year], Sheet8[weeknum] )
        ),
        "m/dd"
    )
)

     

     

    Best Reagrds

    Maggie

    • Josearce's avatar
      Josearce
      Regular Visitor
      7 years ago

      Thanks Maggie...I did something with the formula to present it more organized:

       

      Weekfromto =

       

      VAR Desde = format(Calculate(min('Calendar Table'[Date]),ALLEXCEPT('Calendar Table','Calendar Table'[Year],'Calendar Table'[Week#])),"mmm-dd-yy")

      VAR Hasta = format(Calculate(max('Calendar Table'[Date]),ALLEXCEPT('Calendar Table','Calendar Table'[Year],'Calendar Table'[Week#])),"mmm-dd-yy")

       

      RETURN

       

      Desde &" - "& Hasta

       

      Desde means From and Hasta means Through