Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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 !!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
56 | |
27 | |
24 | |
14 | |
9 |
User | Count |
---|---|
77 | |
61 | |
47 | |
17 | |
12 |