Repeated Tickets each month comparing last 12 months

[EV]

I am trying to calculate repeated cases using the Repeat case string each month by looking in the last 12 months.

 take all of the cases that occurred in the current month (per the slicer) and then count how many of them had an identical case( identical case is identified using Repeat Case String) in the prior 12 months.
Can anyone please help me?

[Ashish_Mathur]

Hi,

Please show the expected result.

[EV]

for example, i have a ticket 12345, and case string is 'debugflow125', in the last 12 months the 'debugflow125' appeared about 40 times, so for that ticket, i report there were 40 identical cases occurred in the last 12 months.

does this help?

[MFelix]

Hi @EV ,

That's what my measure does first parte evaluates the string and the second part evaluates the time range.

Can you share the measure you created in your model? Just to check the syntax since you refered previouly it was not working properly.

Regards,

MFelix

[MFelix]

Hi @EV ,

Assuming that you are making the use of the slicer based on open date create the following measure:

Count Repeated =
VAR date_selected =
    MAX ( 'Cases'[Open Date] )
VAR String_ID =
    SELECTEDVALUE ( 'Cases'[Repeat Case String] )
RETURN
    COUNTROWS (
        FILTER (
            GROUPBY (
                ALL ( 'Cases'[Case Number]; 'Cases'[Repeat Case String]; 'Cases'[Open Date] );
                'Cases'[Case Number];
                'Cases'[Repeat Case String];
                'Cases'[Open Date]
            );
            'Cases'[Repeat Case String] = String_ID
                && 'Cases'[Open Date] <= date_selected
                && 'Cases'[Open Date]
                    >= DATE ( YEAR ( date_selected ) - 1; MONTH ( date_selected ); 1 )
        )
    )

Be aware that looking at your data the Case Number is a uniqueID if not use a column that is unique ID or you will get less counts than actuals.

Regards,

MFelix

[EV]

Hello @MFelix , I was able to make the code work without red lines, however this DAX requires me to select the case string. 
Is it possible for you to help me without having to select the case string but should compare the case strings from current month to look up in the 12 months to get the count of case strings repeated?

[MFelix]

Hi @EV ,

Can you share the measure formula you are using the last part of my measure is the 12 months filter:

                && 'Cases'[Open Date] <= date_selected
                && 'Cases'[Open Date]
                    >= DATE ( YEAR ( date_selected ) - 1; MONTH ( date_selected ); 1 )

Regards,

MFelix

[EV]

12 months filter, worked perfect, but I had to highlight the case string to show the count for case number, it needs to be the other way round. For each case ticket, pick the case string and look back in the 12 months as return count of those case strings appeared.

for example, i have a ticket 12345, and case string is 'debugflow125', in the last 12 months the 'debugflow125' appeared about 40 times, so for that ticket, i report there were 40 identical cases occurred in the last 12 months. I hope you could help me. Please bear with me on the limited knowledge.

[EV]

Thank you so much for your response. I was out and couldnt validate this. As soon as i got access to the work, I checked it. 

For some reason at the line SELECTEDVALUE, it stops working, i am not able to see errors. but it shows red line on all the lines starting from SelectedValue. Is it because I can't create calculated column as I am connected to live SSAS tabular model and it allows only to create measures?