Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Capture Max Date based on selected week

Hi,
I need to capture the date of the Thursday if the selected week is not the current week ( is a previous week), if the selected week is the current week then the date must be the date of 2 days ago as attached screenshot.

 

Need Help

9 Replies

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

    Anonymous , if am hopeful you have week start and end date

    if(today()>=[week start] && today()<=[week end] ,[Date]+2,'Date'[Date]+-1*if(WEEKDAY('Date'[Date],2)<4,WEEKDAY('Date'[Date],2)+3,WEEKDAY('Date'[Date],4)-4))

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak , is this a measure or calculated column?

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

      Anonymous ,

      Like

      minx('Date',if(today()>=[week start] && today()<=[week end] ,[Date]+2,'Date'[Date]+-1*if(WEEKDAY('Date'[Date],2)<4,WEEKDAY('Date'[Date],2)+3,WEEKDAY('Date'[Date],4)-4)))

       

      or


      if(today()>=Min('Date'[week start]) && today()<=MAx('Date'[week end]) ,Min('Date'[Date])+2,Min('Date''Date'[Date])+-1*if(WEEKDAY(MIn('Date'[Date]),2)<4,WEEKDAY(Min('Date'[Date]),2)+3,WEEKDAY(min('Date'[Date]),4)-4))

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    hi  Anonymous 

    The logic is simple, but what is your data structure? from your description, it seems that selected financial week and selected on are not at the same table?

    If possible, please share your sample data structure, that will be a great help.

    Please see this post regarding How to Get Your Question Answered Quickly:
    https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

     

    Regards,

    Lin

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      v-lili6-msft , the Selected on column in the screenshot, is not a column in my data. This column means when the current weeks is that week, meaning when the current week1 and selected week is week 52, I want to show 25-Jun-2020.

       

      I hope this helped.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Guys,

         

        I am sorry about this, but that does not work. Let me explain what is my actual problem, below is my data.

        I want to count the jobs where:

        the flage = 1 of the last entry number for each Job number ignoring the Location

        and the Date is yesterday

        and only  counted for the location of the last entry number.

         

        for above example, for Job = J12345, the last Entry no = 17, the flag =0 and the Date = 13/07/2020.

        this job should not be counted when I select location = CC or BB.

        for the second Job no = J22222, the last Entry no = 21, the flag =1 and the Date = 13/07/2020, only location TT should be counted, it should ignor location BB.

         

        My code is counting 1 for  job number  Job = J12345 for entry number 14, And counting 2 for  Job = J22222, one for location TT and one for location BB, Table and Table2 are joined on Job number:

        VAR dates =
        FORMAT( MAX ( 'Posting Date'[Date] ), "yyyy-mm-dd")
        VAR MaxEntryNo =CALCULATE( MAX( 'Table'[Entry Number]) , ALLEXCEPT( Table2, Table2[Job Number]))
        VAR LocationCode = CALCULATE(MAX('Table'[Location Code]), 'Table'[Entry Number] = MaxEntryNo, ALLEXCEPT( Table2, Table2[Job Number]))


        RETURN

        CALCULATE (
        DISTINCTCOUNT ( 'Table'[Job Number] ),
        FILTER (
        'Table',
        'Table'[Flag] = 1
        && 'Table'[Entry Number] = MaxEntryNo
        && 'Table'[Location Code] = LocationCode
        && FORMAT('Table'[Date], "yyyy-mm-dd") = dates
        )
        )