Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago
Solved

Value Calculation between relative dates

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.

UsersDatesValues
Usuario114/07/23110
Usuario128/06/23120
Usuario214/07/23130
Usuario207/07/23140
Usuario213/06/23150

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's avatar
    Anonymous
    3 years ago

    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.           

     

2 Replies

  • AmiraBedh's avatar
    AmiraBedh
    Super User
    3 years ago

    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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.