Forum Discussion

Navaneetharaju_'s avatar
Navaneetharaju_
Icon for Helper II rankHelper II
2 years ago

Need Help in Dax or column

Hi All,

 

I'm creating the task run book report using task details, based on the cron day field

I have days numbers in Cron_days column, in that field some field value has these values also,

Last thur, 1st mon, Last Fri, 2nd fri like this i have the field values.  I have to present what are the tasks will run under the selected date for example if i select one date 29-Mar-2024, last friday of the week , Whenever i select 29-Mar-2024, Cron Day - 29 and Cron Day = last fri those task details should come,  If i select 01-Apr-2024 it should return the task which will come under the Cron day = 1 and 1st mon because 01-Apr-2024 is the first monday of the april month . same way we to have find the remaining field values also.

Task_namecron_day
Task 81
Task 72
Task 113
Task 94
Task 55
Task 18
Task 310
Task 1426
Task 61st fri
Task 131st mon
Task 101st mon,1st thu
Task 21st sat,1st sun
Task 41st sat,3rd sat
Task 12last thu

 

I have provided the details of the data.

IF('Tasks'[cron_day]=DAY(SELECTEDVALUE('Date'[Date]) , 1)

with this measure, i could find on the which date its running, but for the last, last fri, 1st mon, 2nd thur these fields values, i don't know how to apply the measures or column to find the task on the selected date.




Need Help
Show and Tell
Tips and Tricks

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Navaneetharaju_ 

    Actually that I haven't understand the logical of your post completely, do you have a calendar table?

    Here is a reference to find the last friday of a month:

    I create a calendar table:

    Then add a new column:

    Column = WEEKDAY('Date'[Date],2)

    This returns the weekday of the date:

    Finally, I create a measure:

    LastFri =
VAR _currentMonth =
    MONTH ( MAX ( 'Date'[Date] ) )
VAR _Fri =
    FILTER (
        ALL ( 'Date' ),
        'Date'[Column] = 5
            && MONTH ( 'Date'[Date] ) = _currentMonth
    )
RETURN
    MAXX ( _Fri, [Date] )

    Then you can get the last friday of current week:

     

    Best Regards

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

    • Navaneetharaju_'s avatar
      Navaneetharaju_
      Icon for Helper II rankHelper II
      2 years ago

      Hi Anonymous 

      Thanks for your response, I'm not expecting this as a result. sorry for that, some how i couldn't explained clearly, 

      I have multiple tasks and that will run based on the cron_day column, which means that day of the every month 
      For example, if i select 01-Apr-2024 using my Date slicer, 
      I want to display the what all are the task will come under the selected date, which means this task will run on the Cron_day which mentioned in the table.
      As per the selected date as 01-Apr-2024, the below task should come in the table

      Task Name 

      Task 8

      Task 13
      Task 10 

      Task 8 , cron day is 1, selected date Day is 1, so it's coming
      Task 13 cron day is 1st mon(monday) , selected date is 01-Apr-2024 and its the 1st monday of the month so it should come
      Task 10 Cron day is 1st mon(monday), selected date is 01-Apr-2024 and its the 1st monday of the month so it should come

      As above i said for cron_day number value, we can match the day of the selected date with the Cron day field value and we can get the result. 

      But 1st mon( for now i'm just saying on example)  , how can we find this date is the 1st mon of the month , it my differ right on the month.

      I hope i clearly explain the requirement