Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Problem with splitted WEEKNUM

Hello everyone,

I have a problem with the calendar week.
I want to make a list of the hours worked per calendar week.
If the calendar week is in two months, it will be displayed twice. Logically, this is correct, but I would like to summarize this in the display.
Do you have an idea how I can solve this?
Thanks in advance and best regards

 

This is the formula I used to create the calendar:

 

Kalender =
VAR DATES = CALENDAR(
DATE(YEAR(MIN(Zeiterfassung[Datum])),1,1),
DATE(YEAR(MAX(Zeiterfassung[Datum])),12,31)
)
RETURN
ADDCOLUMNS(DATES,
"Jahr", YEAR([Date]),
"Quartal", QUARTER([Date]),
"Monat", FORMAT([Date],"MMM"),
"Monat#", MONTH([Date]),
"Kalenderwoche", WEEKNUM([Date],2),
"Wochentag", FORMAT([Date],"ddd"),
"Wochentag#", WEEKDAY([Date],2)
)
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    Hi, Anonymous 

     

    It’s my pleasure to answer for you.

    According to your description,I think you can create a column, then use it in X axis.

    Like this:

    measure =
VAR a =
    CALCULATE (
        DISTINCTCOUNT ( Table[Jahr] ),
        FILTER (
            ALL ( Table ),
            [Jahr] = EARLIER ( Table[Jahr] )
                && [Kalenderwoche] = EARLIER ( Table[Kalenderwoche] )
        )
    )
VAR b =
    CONCATENATEX (
        CALCULATETABLE (
            DISTINCT ( table[Monat] ),
            FILTER (
                ALL ( Table ),
                [Jahr] = EARLIER ( Table[Jahr] )
                    && [Kalenderwoche] = EARLIER ( Table[Kalenderwoche] )
            )
        ),
        table[Monat],
        "+"
    )
RETURN
    IF ( a > 1, [Jahr] & b & [Kalenderwoche], [Jahr] & [Monat] & [Kalenderwoche] )

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

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

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    @LuSt92 , Create a week start or weekend or weekend and use it.

    Start date of the week : 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
    End date of week : 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)

    Month of the week Home - eomonth([Start date of the week],-1)+1 //week start of the month

    Month of the week - format([Start date of week],"MMM-YYYY")

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you! 🙂

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Anonymous 

     

    It’s my pleasure to answer for you.

    According to your description,I think you can create a column, then use it in X axis.

    Like this:

    measure =
VAR a =
    CALCULATE (
        DISTINCTCOUNT ( Table[Jahr] ),
        FILTER (
            ALL ( Table ),
            [Jahr] = EARLIER ( Table[Jahr] )
                && [Kalenderwoche] = EARLIER ( Table[Kalenderwoche] )
        )
    )
VAR b =
    CONCATENATEX (
        CALCULATETABLE (
            DISTINCT ( table[Monat] ),
            FILTER (
                ALL ( Table ),
                [Jahr] = EARLIER ( Table[Jahr] )
                    && [Kalenderwoche] = EARLIER ( Table[Kalenderwoche] )
            )
        ),
        table[Monat],
        "+"
    )
RETURN
    IF ( a > 1, [Jahr] & b & [Kalenderwoche], [Jahr] & [Monat] & [Kalenderwoche] )

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

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