Forum Discussion
Return a value if selected date is between two dates
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.
Super UserDoes 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
- lbendlin
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.
Each machine has a maintenance plan as given below. Machine capacity is Zero during maintenance.
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;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:
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.