Need help in creating the Dax(measures) to find the last day

[Navaneetharaju_]

Hi All, 

I have to create a measure or calculated column to find the last day based on the selected date.

Provided the sample data :

Task Name	Cron_day
Task 1	1
Task 2	last
Task 3	3
Task 4	10
Task 5	25

Sample data that we provided was showing, on which day the task should run, when ever i select particular date from the date_dim table, those date task should reply, 

To finding the day it's quite simple, like
IF(day(selectedvalue(date_dim[date])=task(cron_day), 1, 0)
but 
task 2 it contains the field value as a last, which means every month last day only it should come, in our every month last day number will change like feb is 28 or 29, mar 31, apr 30 till dec (last day of the month will different)

whenever is select the last day of the any month in slicer(date_dim[date]) , for example (31-03-2024) the task 2 should display in chart or visual.

Thanks,
Raju

[johnbasha33]

@Navaneetharaju_ 

To achieve this, you can create a measure in Power BI that dynamically calculates the last day of the month based on the selected date from the date_dim table. Then, you can use this measure to determine if the task should be displayed for the selected date.

Here's how you can create the measure:
LastDayOfMonth =
VAR SelectedDate = SELECTEDVALUE(date_dim[date])
RETURN
IF (
SELECTEDVALUE(date_dim[Day]) = DAY(EOMONTH(SelectedDate, 0)),
1,
IF (
SELECTEDVALUE(date_dim[Day]) = DAY(EOMONTH(SelectedDate, -1)),
1,
0
)
)

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

[Navaneetharaju_]

Hi @johnbasha33 ,

some how i conveyed wrongly the request, I don't want find the last day of the month, 

I want to map Task 2  with all last day of the month dynamically, 

whenever i select last day date in the slicer, we have to display the tasks which will run on that date.

I hope, i have explained clearly.