Carryover of cases from Last year

[Anonymous]

I have cases carryover from 2022 to 23. I need a dax formula showing the cases closed /open in 2023 which are from 2022. Can we achieve this? Any help is greatly appreciated. I currently have open and close cases by month wise for 2023. However, this chart included the cases from 2022 as well. How do i segregate the segregate / show 2022 cases seperately in chart. 

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can refer the following links to get it:

Calculate OPEN CASES over time in Power BI

1. Have a date dimension table first

2. Create a measure as below

Employee Count = 
VAR __DATE = MAX ( 'Date'[Date] )
RETURN
    SUMX (
        EmployeeTable,
        IF (
            EmployeeTable[DateStarted] <= __DATE
                && OR ( EmployeeTable[Leavedates] >= __DATE , ISBLANK(EmployeeTable[Leavedates]) ),
            1,
            BLANK ()
        )
    )

3. Create visual

Power BI, DAX - Count number of closed cases by day, when "open date" is establishing the relationship to the Date table

Need dashboard for Resolved VS Created Tickets

Best Regards

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can update the formula of your measure as below and check if it can return the correct result...

Measure =
CALCULATE (
    COUNT ( 'PlatSol Everything Board Filter'[Issue id] ),
    FILTER (
        'PlatSol Everything Board Filter',
        'Platsol Everything Board Filter'[Status] = "Closed"
            && YEAR ( 'Platsol Everything Board Filter'[Epics Resolved] ) = 2023
            && YEAR ( 'Platsol Everything Board Filter'[Epics Created] ) = 2022
    )
)

If the above one can't help you, please provide some raw data in your table (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Anonymous]

hi @Anonymous 

I am trying to use this measure. to show the 2022 cases in the 2023 chart. But unfortunately the created date slicer take precedence so it doesn't show up any cases. However when i go to 2022 chart, the cases closed in 2023 are showing up there. I need it vice versa. Can you help. 

Measure = CALCULATE(

    COUNTA('PlatSol Everything Board Filter'[Resolved]),USERELATIONSHIP('PlatSol Everything Board Filter'[Resolved],Dates[Master Date]),

    FILTER (

        'PlatSol Everything Board Filter',

        'Platsol Everything Board Filter'[Status] = "Closed"

             && YEAR ( 'PlatSol Everything Board Filter'[Created]) = 2022

             && YEAR ( 'PlatSol Everything Board Filter'[Resolved]) = 2023

    )

)

2022 Chart:

2023 Chart:

[Anonymous]

@Anonymous I have a quick question. I have slicer set for created date. All the charts in that Page are interacting with that slicer. Is it possible to use a second slicer based on resolved date. As you can see below the orange ones are the cases opened in 2022 and closed in 2023. Having said, it is currently showing those cases in 2022 chart, as the slicer is based on Created date (which will be obviosly 2022). I wanted those cases to be shown on 2023 chart by using a second slicer based resolved date. Is it possible to make resolved slicer to interact only with this chart and created slicer to interact with other charts? If yes, kindly let me know. I couldn't find that in forum.  

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can refer the following links to get it:

Calculate OPEN CASES over time in Power BI

1. Have a date dimension table first

2. Create a measure as below

Employee Count = 
VAR __DATE = MAX ( 'Date'[Date] )
RETURN
    SUMX (
        EmployeeTable,
        IF (
            EmployeeTable[DateStarted] <= __DATE
                && OR ( EmployeeTable[Leavedates] >= __DATE , ISBLANK(EmployeeTable[Leavedates]) ),
            1,
            BLANK ()
        )
    )

3. Create visual

Power BI, DAX - Count number of closed cases by day, when "open date" is establishing the relationship to the Date table

Need dashboard for Resolved VS Created Tickets

Best Regards

[Anonymous]

Thanks for your help. I am still learning powerbi. I do not see any relevant link, which can help me to count the 2022 cases.. The links u referred is to calculate the open cases for a particular date. I need formula to filter 2022  cases which is closed in 2023.  I am writing the below formula, the third line in the formula should filter 2022 cases from status closed. How do i do that? Any help is appreciated.

CALCULATE(

    COUNT('PlatSol Everything Board Filter'[Issue id]);

    'Platsol Everything Board Filter'[Status] = "Closed";

)

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can update the formula of your measure as below and check if it can return the correct result...

Measure =
CALCULATE (
    COUNT ( 'PlatSol Everything Board Filter'[Issue id] ),
    FILTER (
        'PlatSol Everything Board Filter',
        'Platsol Everything Board Filter'[Status] = "Closed"
            && YEAR ( 'Platsol Everything Board Filter'[Epics Resolved] ) = 2023
            && YEAR ( 'Platsol Everything Board Filter'[Epics Created] ) = 2022
    )
)

If the above one can't help you, please provide some raw data in your table (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Anonymous · Accepted solution]

Thank you so much yingyinr. It worked like a charm. Thanks a lot.