cancel
Showing results for
Did you mean:
Frequent Visitor

## How to calculate measure with the development of active cases depending on the calendar table?

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?

1 ACCEPTED SOLUTION
Community Support

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!

2 REPLIES 2
Community Support

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!

Super User

@MarcLykke , I have blog on similar topic, see if that can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

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]

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors