topic Re: How to Adjust Service Measures Based on Date Range in DAX Commands and Tips

How to Adjust Service Measures Based on Date Range

alanvicsansz — Tue, 08 Oct 2024 04:52:03 GMT

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:

Current Year: Represents the number of calls handled in the current year filter.
Previous Year: Represents the number of calls handled in the previous year filter.
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:

Current Year:
- If the maximum filtered date is, for example, January 31, 2023, the displayed value should only be for January 2023.
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.
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

Re: How to Adjust Service Measures Based on Date Range

quantumudit — Tue, 08 Oct 2024 07:54:30 GMT

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

Re: How to Adjust Service Measures Based on Date Range

alanvicsansz — Tue, 08 Oct 2024 21:17:04 GMT

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!

Re: How to Adjust Service Measures Based on Date Range

Anonymous — Wed, 09 Oct 2024 08:53:01 GMT

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

Re: How to Adjust Service Measures Based on Date Range

alanvicsansz — Wed, 09 Oct 2024 20:16:36 GMT

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