Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello I'm trying to make a dynamic date slicer that would return a value of working days between 2 dates. Further I would like to use this dynamic value in a calculation.
I need this to make a report to measure time that should be spent for particular client.
I ve got 2 tables the first one is the data consisting of time recorded per every employee that work on particular company issues.
Columns: Ticket number, time spent (in hours in decimal format), workdate, employee, Ticket/issue name Weeknumber (calculated by me), Organisation,, current month (by me).
This data is connected to another table that consists of information about time budget for particular company (MAIN is our company so it should not be counted in budget) columns: Organisation, Proportion (proportion of time that should be devoted for particular organizastion for example if the wage is 0.145 it means that 14,5% of time should be spent on this client) and here I would like to have a column that is calculated in the following logic:
=Proportion * 7 (number of employees) * 8 (working hours in a day ) * workingdays (value that should be returned for a slicer, it should be the same for everycompany)
So that finaly I would like to have a report that consist of a slicer (to adjust number of days )
And a bar chart that hours devoted for particular customer versus time budget this chart should be depanded on the number of days that are chosen by the user.
To get the number of days i defined the measure with the following formula
Remaining Days =
VAR a =
FIRSTDATE ( Data[workdate] )
VAR b =
CALCULATE(MAX(Data[workdate]);ALLSELECTED(Data[workdate]))
RETURN
DATEDIFF(a;b; DAY)
And I am able to return the number of days but I cant use it in calculation as I would like to.
Hi @belogward ,
According to your requirements, you need to create a measure but not a calculated column. The value in the column is static and it will not change directly due to changes in slicer.
Ok fine. I got the number of days that are returned by a slicer, but now the problem is differrent.
I would like to use it in the calculation that would return value of time that should be devoted for particular customer
Logic:
Budget =Proportion (percentage of time that should be devoted for particular customer, value is stored in the Proportion column and is different for every company)* 7 (number of employees) * 8 (working hours in a day ) * workingdays (value that should be returned for a slicer, it should be the same for everycompany)
The problem is that I can't use the Proportion value in the measure responsible for returning value of time budget
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |