Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi everyone
I am trying to calculate a measure that shows me the number of active cases in a given timeperiod.
Table 1 includes all cases
Case ID | Start date | End date | Status |
1 | 15/01/2020 | Active | |
2 | 04/10/2020 | 10/11/2021 | Closed |
3 | 03/05/2019 | 04/05/2020 | Closed |
4 | 20/10/2021 | Active |
I have created a relationship to a calendar-table based on the start date and end date in table 1. The relationship between calendar date and start date is active an the relationship between calendar date and end date is not.
Date | Year | Month | Week | Number of months ago | Number of weeks ago |
xx | xx | xx | xx | xx | xx |
I am trying to calculate a measure that allows me to compare the number of active cases now with the number of active cases the same time last year.
The visualization should look like this:
Case Group | Active cases (now) | Active cases (12 months ago) |
Case Group 1 | xx | xx |
Case Group 2 | xx | xx |
Case Group 3 | xx | xx |
Case Group 4 | xx | xx |
Can anyone help me create this calculated measure?
Solved! Go to Solution.
Hi @MarcLykke
Maybe you can try these two measures.
Active cases (now) =
VAR CountAct =
CALCULATE (
COUNT ( Table1[Start date] ),
Table1[End date] > MAX ( 'calendar-table'[Date] )
|| Table1[Status] = "Active"
)
RETURN
IF ( ISBLANK ( CountAct ), 0, CountAct )
Active cases (12 months ago) =
VAR MinDate =
MIN ( 'calendar-table'[Date] ) --Min Date in current period
VAR MaxDate =
MAX ( 'calendar-table'[Date] ) --Max Date in current period
VAR PrevYearMinDay =
DATE ( YEAR ( MinDate ) - 1, MONTH ( MinDate ), DAY ( MinDate ) ) --Min Date 12 months ago
VAR PrevYearMaxDay =
DATE ( YEAR ( MaxDate ) - 1, MONTH ( MaxDate ), DAY ( MaxDate ) ) --Max Date 12 months ago
VAR count_ =
CALCULATE (
COUNT ( Table1[Start date] ),
FILTER (
'calendar-table',
'calendar-table'[Date] >= MinDate
&& 'calendar-table'[Date] <= MaxDate
),
( Table1[Start date] < PrevYearMaxDay
&& Table1[End date] > PrevYearMaxDay )
|| Table1[Status] = "Active"
)
RETURN
IF ( ISBLANK ( count_ ), 0, count_ )
Then, the result should look like this:
For more details, please refer the attached pbix file.
Best Regards,
Community Support Team _ Caiyun
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If you still have problems on it or I misunderstand your needs, please feel free to let us know. Thanks a lot!
Hi @MarcLykke
Maybe you can try these two measures.
Active cases (now) =
VAR CountAct =
CALCULATE (
COUNT ( Table1[Start date] ),
Table1[End date] > MAX ( 'calendar-table'[Date] )
|| Table1[Status] = "Active"
)
RETURN
IF ( ISBLANK ( CountAct ), 0, CountAct )
Active cases (12 months ago) =
VAR MinDate =
MIN ( 'calendar-table'[Date] ) --Min Date in current period
VAR MaxDate =
MAX ( 'calendar-table'[Date] ) --Max Date in current period
VAR PrevYearMinDay =
DATE ( YEAR ( MinDate ) - 1, MONTH ( MinDate ), DAY ( MinDate ) ) --Min Date 12 months ago
VAR PrevYearMaxDay =
DATE ( YEAR ( MaxDate ) - 1, MONTH ( MaxDate ), DAY ( MaxDate ) ) --Max Date 12 months ago
VAR count_ =
CALCULATE (
COUNT ( Table1[Start date] ),
FILTER (
'calendar-table',
'calendar-table'[Date] >= MinDate
&& 'calendar-table'[Date] <= MaxDate
),
( Table1[Start date] < PrevYearMaxDay
&& Table1[End date] > PrevYearMaxDay )
|| Table1[Status] = "Active"
)
RETURN
IF ( ISBLANK ( count_ ), 0, count_ )
Then, the result should look like this:
For more details, please refer the attached pbix file.
Best Regards,
Community Support Team _ Caiyun
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If you still have problems on it or I misunderstand your needs, please feel free to let us know. Thanks a lot!
@MarcLykke , I have blog on similar topic, see if that can help
Check the file for last year
example
Hire = CALCULATE([Employees], USERELATIONSHIP('Date'[Date], Emp[Start Date]))
Teminated = CALCULATE([Employees], USERELATIONSHIP('Date'[Date], Emp[End Date]), not (ISBLANK(Emp[End Date])))
Cumm Hire 1Year Back = CALCULATE([Hire], FILTER(ALL('Date') , 'Date'[Date] <= maxX('Date',DATEADD( 'Date'[Date],-1,year))))
Teminated Hire 1Year Back = CALCULATE([Teminated], FILTER(ALL('Date') , 'Date'[Date] <= maxX('Date',DATEADD( 'Date'[Date],-1,year))))
Current 1 Year Back = [Cumm Hire 1Year Back] -[Cumm Termination 1 year Back]
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
18 | |
18 | |
18 | |
17 |
User | Count |
---|---|
33 | |
25 | |
18 | |
15 | |
13 |