Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Display data for Monday every week.

Requirement is to display data only for Monday for entire week. Next week, it should again reflect values for Monday this week only.

for example : Today is 12th May, Wednesday but report should reflect data for 10th May and if Today is 6th May, it should reflect data for 03th May. 

I hope requirement is clear.

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi Anonymous ,

     

    Create 2 columns as below:

    Weeknum = WEEKNUM('Table'[Date],2)
    First Monday = 
var _mindate=CALCULATE(MIN('Table'[Date]),FILTER('Table','Table'[Weeknum]=EARLIER('Table'[Weeknum])))
var _weekday=WEEKDAY(_mindate,2)
Return
IF(_weekday<>1,DATE(YEAR(_mindate)-1,12,31-(_weekday-1)+1),_mindate)

    And you will see:

     

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

5 Replies

  • HarishKM's avatar
    HarishKM
    Super User
    5 years ago

    Anonymous  Hey , 

    You can create a cal. coloumn for week .
    Week = weeknum(date[date],2)

    2 is week starts from monday 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    Hi, Anonymous 

    Please check the below picture and a sample pbix file's link down below.

    I tried to create a sample pbix file based on the explanation.

     

     

    Sales Total Only reflects Monday =
    CALCULATE (
    [Sales Total],
    FILTER (
    ALL ( Dates ),
    Dates[Week & Year] = MAX ( Dates[Week & Year] )
    && Dates[Day of Week] = 0
    )
    )
     
     
     

    Hi, My name is Jihwan Kim.


    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , One way is to create a column in date table and default on that

     

    Date Type =
    var _1 = today() +-1*WEEKDAY(today() ,2)+1
    return
    SWITCH(TRUE(),'Date'[Date]=_1,"Last Monday"
    ,'Date'[Date]&"")

     

    or create a measure like 

     

    measure =

    var _min = minx(allselected('Date1'), Date1[Date])
    var _max = today() +-1*WEEKDAY(today() ,2)+1
    return
    calculate(Sum('Table'[Value]), filter('Date', 'Date'[Date] <=_max && 'Date'[Date] >=_min ))

     

     

    Refer for Default Date Today/ This Month / This Year: https://www.youtube.com/watch?v=hfn05preQYA&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=35

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    Hi Anonymous ,

     

    Create 2 columns as below:

    Weeknum = WEEKNUM('Table'[Date],2)
    First Monday = 
var _mindate=CALCULATE(MIN('Table'[Date]),FILTER('Table','Table'[Weeknum]=EARLIER('Table'[Weeknum])))
var _weekday=WEEKDAY(_mindate,2)
Return
IF(_weekday<>1,DATE(YEAR(_mindate)-1,12,31-(_weekday-1)+1),_mindate)

    And you will see:

     

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

    • AppleUSN's avatar
      AppleUSN
      Frequent Visitor
      2 years ago

      This solution is flawed.  It only works for 1 year because the Weeknum column starts repeating unless you continue to add the year into the week number.  Such as 1-52 then 53- 104 as the next week.  The solution I used is to have a Week Number and Year to do the filter. 

       

      Year-Week = YEAR([Date]) & FORMAT(WEEKNUM([Date], 21), "-\W00") 
       
      This creates Year-Week "2023-W39".  Then when the first monday is found it is found on the specific week of that specific year.  The original formula here was always using the first years week number even when it was 5 years later.