cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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.

 

MachineMaint 1 start dateMaint 1 end dateMaint 2 start dateMaint 2 end date
Machine 122-Sep-202-Oct-2016-Nov-2026-Nov-20
Machine 23-Oct-2013-Oct-2027-Nov-207-Dec-20
Machine 314-Oct-2024-Oct-208-Dec-2018-Dec-20
Machine 425-Oct-204-Nov-2019-Dec-2029-Dec-20
Machine 55-Nov-2015-Nov-2030-Dec-209-Jan-21

@ links to members

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Returns a value if the selected date is between two f

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:

1.png

Tabla SD:

2.png

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:

3.png

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.

View solution in original post

7 REPLIES 7
Highlighted
Super User II
Super User II

Re: Return a value if selected date is between two dates

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.

Highlighted
Frequent Visitor

Re: Returns a value if the selected date is between two f

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.

MachineSD TypeSD StartSD EndCapacity
Machine 1PMI22-Sep-202-Oct-200
Machine 2PMI3-Oct-2013-Oct-200
Machine 3PMI14-Oct-2024-Oct-200
Machine 4PMI25-Oct-204-Nov-200
Machine 5PMI5-Nov-2015-Nov-200
Highlighted
Super User II
Super User II

Re: Returns a value if the selected date is between two f

Does your capacity counter always reset to zero on the first of january?

Highlighted
Frequent Visitor

Re: Returns a value if the selected date is between two f

Capacity of a machine is "0" when the machine is under maintenance i.e. between SD start date and SD end date. Machine capacity would be 30 when it is not under maintenance.

Thanks,

Anil

Highlighted
Super User II
Super User II

Re: Returns a value if the selected date is between two f

that conflicts with your initial statement.  Please show expected outcome for a couple of dates around your sample data.

Highlighted
Frequent Visitor

Re: Returns a value if the selected date is between two f

Hi !

Please find details. I have a month wise Machine Capacity table. If a machine is running I get this output from each machine.

Cap.png

Each machine has a maintenance plan as given below. Machine capacity is Zero during maintenance.

SD.png

 

 

 

 

 

Hence in a 3rd table I wanna see available capacity for each date and for that I have put below DAX: See a DAX for Machine 2

Machine 2 =
CALCULATE
    (
    SUM ( Cap[Machine 2] ),
    FILTER (
        ALL ( Cap ),
        Cap[Month]= FORMAT('Date'[Date],"MMM")),
    FILTER(
        ALL(SD),
        OR(
            SD[Machine]="Machine 2"&& SELECTEDVALUE('Date'[Date])<MIN(SD[SD Start ]),
            SD[Machine]="Machine 2"&&SELECTEDVALUE('Date'[Date])<MAX(SD[SD End])
        )
        )
    )
 
with this, i intend to get machine wise daily capacity as per below table;
Date.png
However, my DAX has some issue as for the dates on which a machine is under maintenance, I get the capacity ohter than Zero.
 
Please help.
Highlighted
Community Support
Community Support

Re: Returns a value if the selected date is between two f

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:

1.png

Tabla SD:

2.png

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:

3.png

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.

View solution in original post

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors