Forum Discussion
Return a value if selected date is between two dates
Hello,
I am creating a power bi dashboard for machines shutdown planning. Let's say I have 5 machines. Each machine undergoes one or two maintenances every year. During each maintenance period, capacity of a machine is "0". Till a machine undergoes first maintenance, it's capacity is "1". After first maintenance, the capacity is "2" and after second maitenance, the capacity is "3".
Basically, I need something similar to 3D lookup where it checks if on selected date, if the machine is with what capacity i.e. 0/1/2/3 and return that value.
I have 3 tables in my dashboard. One is list of machines, the other is date and third one is machine maitenace schedule as given below. I have done this in excel with nesting of multiple "IF" function but strugging to develop power bi DAX for the same.
| Machine | Maint 1 start date | Maint 1 end date | Maint 2 start date | Maint 2 end date |
| Machine 1 | 22-Sep-20 | 2-Oct-20 | 16-Nov-20 | 26-Nov-20 |
| Machine 2 | 3-Oct-20 | 13-Oct-20 | 27-Nov-20 | 7-Dec-20 |
| Machine 3 | 14-Oct-20 | 24-Oct-20 | 8-Dec-20 | 18-Dec-20 |
| Machine 4 | 25-Oct-20 | 4-Nov-20 | 19-Dec-20 | 29-Dec-20 |
| Machine 5 | 5-Nov-20 | 15-Nov-20 | 30-Dec-20 | 9-Jan-21 |
@ links to members
Hello @Anil59060
You may want to display values in this calculation logic:
If DATE is not on the SD date, display the values in the capitalization table (specific machine).
If Date in SD DATE, it displays 0.
I think you can test the IF function to achieve your goal.
I build a table like yours to test me.
Capitalization table:
Tabla SD:
Create a date table:
Date = ADDCOLUMNS(CALENDARAUTO(),"Month",FORMAT([Date],"MMM"))Measure:
Machine 1 = VAR _SD_Start = CALCULATE ( MAX ( SD[SD Start] ), FILTER ( SD, SD[Machine] = "Machine 1" ) ) VAR _SD_End = CALCULATE ( MAX ( SD[ SD End] ), FILTER ( SD, SD[Machine] = "Machine 1" ) ) VAR _Value = CALCULATE ( SUM ( Cap[Machine 1] ), FILTER ( Cap, Cap[Month] = 'Date'[Month] ) ) RETURN IF ( 'Date'[Date] >= _SD_Start && 'Date'[Date] <= _SD_End, 0, _Value )Result:
Other measurements of the machine are the same, you just need to change the three measurements in var.
You can download the pbix file from this link: Return a value if the selected date is between two dates
Best regards
Rico Zhou
If this post helps,then consider Accepting it as the solution to help other members find it faster.
7 Replies
Your table is needlessly complex. Transform it like this
Machine Maint start date Maint end date Machine 1 22-Sep-20 2-Oct-20 Machine 2 3-Oct-20 13-Oct-20 Machine 3 14-Oct-20 24-Oct-20 Machine 4 25-Oct-20 4-Nov-20 Machine 5 5-Nov-20 15-Nov-20 Machine 1 16-Nov-20 26-Nov-20 Machine 2 27-Nov-20 7-Dec-20 Machine 3 8-Dec-20 18-Dec-20 Machine 4 19-Dec-20 29-Dec-20 Machine 5 30-Dec-20 9-Jan-21 Then you can use a calculated column or measure (depending on how you want to filter) to identify for each day which machines are in maintenance on that day etc.
Thanks for reply. If you could help me with writing DAX for a calculated column to return capacity for selected machine if selected date is between SD start date and SD end date.
Machine SD Type SD Start SD End Capacity Machine 1 PMI 22-Sep-20 2-Oct-20 0 Machine 2 PMI 3-Oct-20 13-Oct-20 0 Machine 3 PMI 14-Oct-20 24-Oct-20 0 Machine 4 PMI 25-Oct-20 4-Nov-20 0 Machine 5 PMI 5-Nov-20 15-Nov-20 0 Does your capacity counter always reset to zero on the first of january?
