Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have below data in my excel
I am using From date as a date slicer and calculating SIck days and reporting period.
I have only record from 1st May to 6 May 2021. If I provide a date outside this date range, then I don't get the correct reporting days.
This is my measure to calculate reporting days.
Can anybody help me here?
Regards,
Parag Chapre
Solved! Go to Solution.
Hi @Anonymous ,
I created a sample pbix file(see attachment), please check whether that is what you want.
1. Create a date table
2. Create a relationship between date table and employeesicks table base on date field
3. Create a measure as below
BI_reportingperiod =
VAR FirstReportingDate = MIN( 'Date'[Date])
VAR LastReportingDate = MAX( 'Date'[Date])
RETURN
DATEDIFF ( FirstReportingDate, LastReportingDate+1, DAY)
Best Regards
Hi @Anonymous ,
I created a sample pbix file(see attachment), please check whether that is what you want.
1. Create a date table
2. Create a relationship between date table and employeesicks table base on date field
3. Create a measure as below
BI_reportingperiod =
VAR FirstReportingDate = MIN( 'Date'[Date])
VAR LastReportingDate = MAX( 'Date'[Date])
RETURN
DATEDIFF ( FirstReportingDate, LastReportingDate+1, DAY)
Best Regards
@Anonymous
Please familiarize yourself with how time intelligence in Power BI works. Once you know the basic rules, you'll know immediately why you get what you get. You can start here: Time Intelligence in Power BI Desktop - SQLBI
Incidentally, you get the above result because you're not using a true date table in your model. Please bear in mind that fact tables' attributes should never be used to slice and dice by; in fact, fact tables MUST always be hidden. One exception to this rule is attributes that constitute the so-called degenerate dimension (but you don't have it). Only dimensions must be used for slicing and dicing. Try to deviate from this rule and you'll be sorry sooner rather than later.
@Anonymous , Try if this can work
VAR FirstReportingDate = MINX(allselected(EmployeeSick) ,EmployeeSick[From date])
VAR LastReportingDate = MAXX(allselected(EmployeeSick) , EmployeeSick[From date])
RETURN
DATEDIFF ( FirstReportingDate, LastReportingDate+1, DAY)
No. it's not working. Is there any workaround?
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |