Value Calculation between relative dates

[Syndicate_Admin]

Hello community!

I need to use in my dashboard a comparison of time recorded by users and compare it with the previous month. For this, I chose to use the meter visual.

For informational purposes, the structure of the database is as follows.

Users	Dates	Values
Usuario1	14/07/23	110
Usuario1	28/06/23	120
Usuario2	14/07/23	130
Usuario2	07/07/23	140
Usuario2	13/06/23	150

Now, I have trouble generating a quick measure for this case. I need that, within the visual "Meter" the "Value" is the data of the current month, but the destination value is the sum of the column "Values" of the last month.

Does anyone have any idea how I can represent it correctly visually? Is it correct to use that visual to represent it?

I look forward to your comments,

Best regards.

[Anonymous · Accepted solution]

Hi @Procito0o ,

You can try below measure to get the previous month value.

PreviousMonthValue = CALCULATE(SUM('Table'[Values]),FILTER(ALLSELECTED('Table'),[Users]=MAX('Table'[Users])),PREVIOUSMONTH('Table'[Dates]))

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[AmiraBedh]

Firstly, ensure that the "Dates" column is recognized as date type. If not, you will need to convert it. You can do this using the Power Query Editor, or by simply changing the data type in the Fields pane.

Then create a measure to get the sum of values for the current month:

Current Month Total =
CALCULATE(
SUM('Table'[Values]),
'Table'[Dates] >= EOMONTH(TODAY(), -1) + 1,
'Table'[Dates] <= EOMONTH(TODAY(), 0)
)

This measure will calculate the sum of "Values" for all dates within the current month.

Then create another measure to get the sum of values for the previous month:

Previous Month Total =
CALCULATE(
SUM('Table'[Values]),
'Table'[Dates] >= EOMONTH(TODAY(), -2) + 1,
'Table'[Dates] <= EOMONTH(TODAY(), -1)
)

This measure will calculate the sum of "Values" for all dates within the previous month.

- Put the "Current Month Total" measure in the "Value" field.
- Put the "Previous Month Total" measure in the "Target Value" field.

This will provide a comparison of the total value for the current month versus the previous month.