How to Adjust Service Measures Based on Date Range

[alanvicsansz]

Hello everyone, good evening! I hope you are all doing well.

I would like to share the challenge I am currently facing regarding the performance measures of the customer service team.

Context of the Challenge

Currently, I have three measures that need adjustment:

1. Current Year: Represents the number of calls handled in the current year filter.
2. Previous Year: Represents the number of calls handled in the previous year filter.
3. Total Accumulated: This is the sum of the current year and previous year measures.

Objective

The main objective is to ensure that these measures display values only when the specified date period is met.

Rules for Each Measure:

1. Current Year:
   • If the maximum filtered date is, for example, January 31, 2023, the displayed value should only be for January 2023.
2. Previous Year:
   • If the minimum filtered date is January 1, 2022, and the maximum date is January 31, 2023, all months should display values, restricting to indicate only the calls from the year 2022.
3. Total Accumulated:
   • This measure will be the sum of the calls recorded in the current year and previous year measures.

Final Considerations

It is essential that the implementation of these rules ensures that the displayed values are consistent with the defined date range. If anyone has suggestions or ideas on how to approach this issue, I would appreciate any help. 💪

Photo:

Download link -> Base de testes.pbix - Google Drive

[Anonymous · Accepted solution]

Hi @alanvicsansz ,

I've made a test for your reference:

1\Add two caculate column for Ocorrencias

Year = YEAR(Ocorrencias[data_ocorrencia])

Month = MONTH(Ocorrencias[data_ocorrencia])

2\Set these two measure

Ano atual = 
VAR MaxYear=YEAR(CALCULATE(MAX(Ocorrencias[data_ocorrencia]),ALLSELECTED(Ocorrencias)))
Var MaxMonth= MONTH(CALCULATE(MAX(Ocorrencias[data_ocorrencia]),ALLSELECTED(Ocorrencias)))
RETURN
CALCULATE(COUNTROWS(ALLSELECTED(Ocorrencias)),Ocorrencias[Month]=SELECTEDVALUE(Calendario[MesNum]) && MaxYear=Ocorrencias[Year] && Ocorrencias[Month]<=MaxMonth )

Ano anterior = 
VAR MaxYear=YEAR(CALCULATE(MAX(Ocorrencias[data_ocorrencia]),ALLSELECTED(Ocorrencias)))-1
Var MaxMonth= MONTH(CALCULATE(MAX(Ocorrencias[data_ocorrencia]),ALLSELECTED(Ocorrencias)))
RETURN
CALCULATE(COUNTROWS(ALLSELECTED(Ocorrencias)),Ocorrencias[Month]=SELECTEDVALUE(Calendario[MesNum]) && MaxYear=Ocorrencias[Year])

Best Regards,

Bof

[alanvicsansz]

It worked perfectly, this was the result I was looking for, thank you very much. 💪🤝

[quantumudit]

Hello @alanvicsansz 

To clarify your question further:

For the current year value, you seek the total values for the entire month corresponding to the latest selected date. For instance, if the latest selected date is February 22, 2023, the current year value would be the "sum of values for February 2023." I hope I have understood the logic for calculating the "Current Year" correctly.

Regarding the previous year, I did not fully grasp your requirements. Could you please rephrase and provide some examples to aid my understanding of the issue?

Additionally, it would be helpful if you could provide a snapshot of the "Expected Answer" or data to accompany the explanation of the "Previous Year" calculation logic.

Thank you,

Udit

[alanvicsansz]

Let me know if the screenshot helps. If you need more details, feel free to ask and I'll be happy to provide more info!