Forum Discussion

jaltoft's avatar
jaltoft
Icon for Resolver I rankResolver I
4 years ago
Solved

Define set weeks

I have a calendar (Calendar linked to my data). I want to have the ability within my data to set date ranges, for example I have a table that shows a set of data that I need to report on but I want to set it within  a couple of date ranges including prev>Sat and less than Prev Friday. Can anyone help with this and any other examples for days?

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi jaltoft 

    The information you provide is not very clear, so there is no way to create a sample exactly according to your needs. I created a similar sample, you can refer to it .

    (1)Create a Calendar date table from “2021-01-01” to “2021-03-30” .

    Calendar Date = CALENDAR(DATE(2021,01,01),DATE(2021,03,30))

    (2)Create a column to return the week number for each date in Calendar date table .

    week num = WEEKNUM('Calendar Date'[Date])

    (3)Create a column to return the ranges by column [week num]

    range = SWITCH(TRUE(),'Calendar Date'[week num]>=1 && 'Calendar Date'[week num]<2,"1 to 2 week",
                      'Calendar Date'[week num]>=2 && 'Calendar Date'[week num]<3,"2 to 3 week",
                      'Calendar Date'[week num]>=3 && 'Calendar Date'[week num]<4,"3 to 4 week",
                      'Calendar Date'[week num]>=4 && 'Calendar Date'[week num]<5,"4 to 5 week",
                      'Calendar Date'[week num]>=5 && 'Calendar Date'[week num]<6,"5 to 6 week", "others")

    The final result is as shown :

    I have attached my pbix file .

     

    Best Regards

    Community Support Team _ Ailsa Tao

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

2 Replies

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

    jaltoft , if you are planning set default for date ranges, that is not possible as of now.

     

    You need to have slicers like this examples column and save that on this week

    Week Type = Switch( True(),
    [start week]<=Today() && [end date]>=Today(),"This Week" ,
    [start week]<=Today()-7 && [end date]>=Today()-7,"Last Week" ,
    [Week Name]
    )

     

     

    Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
    Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)

     

    refer if needed

    Any Weekday Week - Start From Any day of Week
    https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi jaltoft 

    The information you provide is not very clear, so there is no way to create a sample exactly according to your needs. I created a similar sample, you can refer to it .

    (1)Create a Calendar date table from “2021-01-01” to “2021-03-30” .

    Calendar Date = CALENDAR(DATE(2021,01,01),DATE(2021,03,30))

    (2)Create a column to return the week number for each date in Calendar date table .

    week num = WEEKNUM('Calendar Date'[Date])

    (3)Create a column to return the ranges by column [week num]

    range = SWITCH(TRUE(),'Calendar Date'[week num]>=1 && 'Calendar Date'[week num]<2,"1 to 2 week",
                      'Calendar Date'[week num]>=2 && 'Calendar Date'[week num]<3,"2 to 3 week",
                      'Calendar Date'[week num]>=3 && 'Calendar Date'[week num]<4,"3 to 4 week",
                      'Calendar Date'[week num]>=4 && 'Calendar Date'[week num]<5,"4 to 5 week",
                      'Calendar Date'[week num]>=5 && 'Calendar Date'[week num]<6,"5 to 6 week", "others")

    The final result is as shown :

    I have attached my pbix file .

     

    Best Regards

    Community Support Team _ Ailsa Tao

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