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

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