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 Team ,
I want to calculate the previous data before the time period
id | date | status | |||||||||
1 | 1-May | open | |||||||||
1 | 30-Apr | closed | start date | end date | Prev open | ||||||
1 | 2-May | open | 4-Jul | 14-Jul | 1 | ||||||
1 | 4-Jul | closed |
[ID Count (Open Before)] =
// There must be a field in the
// table T that is a true date
// not a string. Assume the
// field is called date. Then:
var MinDateSelected = MIN( T[date] )
return
CALCULATE(
DISTINCTCOUNT( T[id] ),
KEEPFILTERS( T[status] = "open" ),
T[date] < MinDateSelected
)
// Note well that you should not have
// models with one table only but
// build a star schema. If you don't
// do this, you are risking creating
// a lot of issues, one of them being
// correct measures that produce incorrect
// results. You've been warned!
Can you please be more specific? It's not entirely clear what you want to achieve. Thanks.
Consider below scenario:
slicer - 12 june to 12 july
We need to check all records before 12 June (created a measure for this - between [start of previous period] and [end of previous period])
ID AuditCreatedDate Status IsActive
1 12 June open True {should not consider this record as AuditedCreatedDate should be before 12 June }
1 11 June open True ---- max (+1)
1 10 June close True
2 11 June close True {should not consider this record as status is close}
2 10 June open True
2 9 June close False
3 13June open True {should not consider this record as AuditedCreatedDate should be before 12 June }
3 10 June open True -- ---max(+1)
3 6 June open False
Ans: Previous Open ticket count = 2
@Anonymous There is nothing wrong with your measure. Its just that requirement does not match with your measure output.
I want to count of Id's of Previous date where latest status is open and the slicer is changing
Condition 1:
Slicer
3-May | 14-Jul |
i need the data before 3 May
Condition 2:
4-Jul | 14-Jul |
Need data before 4 july
d | date | status | |||||||||
1 | 1-May | open | |||||||||
1 | 30-Apr | closed | |||||||||
1 | 2-May | open | |||||||||
1 | 4-Jul | closed |
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |