Create column with calculations with parameters

[Syndicate_Admin]

Hi, I'm new to the powerbi tool and estiy stuck with the following problem, to see if someone can guide me to find a solution.

I have the need to be able to show the days that a vehicle is in the workshop based on a time slot, the user should be able to perform a search depending on the dates he needs.

For this I have created a column with the following function and it works perfectly, the problem is that the dates are fixed and I would like the user to select them through some selector in the report.

Days in workshop = IF(OR('2023'[START DATE] > DATE(2023,2,28), '2023'[END DATE] < DATE(2023,2,1)),

0,

IF('2023'[START DATE] < DATE(2023,2,1),

DATEDIFF(DATE(2023,2,1),

IF('2023'[END DATE] > DATE(2023,2,28), DATE(2023,2,28), '2023'[END DATE]),

DAY),

IF('2023'[END DATE] > DATE(2023,2,28),

DATEDIFF('2023'[START DATE], DATE(2023,2,28), DAY)+1,

DATEDIFF('2023'[START DATE], '2023'[END DATE], DAY)+1

)

))

The need to do it by column is because in this way I can perform averages and other calculations since I have tried with measurements and I have not achieved the same result.

[Syndicate_Admin]

Hello @DANJOR , you managed to find a solution. I stay in the same!

I need to take the parameter and use it in column creation as a comparison. But there is no case.

Best regards

[Syndicate_Admin]

Good I have passed the formula to a measure and it works without problems, but now I find myself with the difficulty of not being able to use average to calculate the averages and can only work with columns. Could you tell me if there is any way to average from a measurement.

Best regards

[Syndicate_Admin]

First of all I thank you for the answer, I have done some tests and I can not get it to work this is the report with the table

and these are the two calculated columns

In which I try to apply the parameter of dates brings everything to zero, as info I have no relationship between table 023 and calendar.

[amitchandak]

@Syndicate_Admin , calculated column can not use slicer value, we have consider measure and use in visual

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[amitchandak]

@Syndicate_Admin , Assuming your logic is correct, a date range from independent date table

Days in workshop =
var _max = maxx(allselected(Date), Date[date])
var _min = minx(allselected(Date), Date[date])
return
Countx('2023', IF(OR('2023'[START DATE] > _max, '2023'[END DATE] < _min),
0,
IF('2023'[START DATE] < _min, DATEDIFF(_min,IF('2023'[END DATE] > _max, _max, '2023'[END DATE]), DAY),
IF('2023'[END DATE] > _max,DATEDIFF('2023'[START DATE], _max, DAY)+1,DATEDIFF('2023'[START DATE], '2023'[END DATE], DAY)+1)
)),'2023'[START DATE])

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.