Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
MarcLykke
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 IDStart dateEnd dateStatus
115/01/2020 Active
204/10/202010/11/2021Closed
303/05/201904/05/2020Closed
420/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.

 

DateYearMonthWeekNumber of months agoNumber of weeks ago
xxxxxxxxxxxx

 

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 GroupActive cases (now)Active cases (12 months ago)
Case Group 1xxxx
Case Group 2xxxx
Case Group 3xxxx
Case Group 4xxxx

 

Can anyone help me create this calculated measure?

1 ACCEPTED SOLUTION
v-cazheng-msft
Community Support
Community Support

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:

vcazhengmsft_0-1638435849098.png

 

vcazhengmsft_1-1638435849101.png

 

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!

View solution in original post

2 REPLIES 2
v-cazheng-msft
Community Support
Community Support

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:

vcazhengmsft_0-1638435849098.png

 

vcazhengmsft_1-1638435849101.png

 

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!

amitchandak
Super User
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]

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors