cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

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
Super User II
Super User II

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.

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

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

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

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

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.

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
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors