Forum Discussion

karinafreitass's avatar
karinafreitass
Icon for Helper III rankHelper III
5 years ago
Solved

Last month measure

Hello Guys, how are you?

Could someone help me find a solution for the following:

I have the following relationship between tables below

in table:

The calculated "MRR fim do mes" column is a measure of cumulative total.

From there, how could I get a measure that would give me the MRR value of the past month?

 

Thanks so much

 

Karina

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi karinafreitass ,

     

    I made a simple sample.

    First of all, there can be no relationship between the two tables. If it is relevant, the value of the previous month cannot be obtained.

     

    Measure =
IF (
    CALCULATE (
        MAX ( 'Table'[Data] ),
        FILTER (
            ALL ( 'Table' ),
            [Data]
                = DATESINPERIOD (
                    'dCalendar'[Data],
                    SELECTEDVALUE ( dCalendar[Data] ),
                    -1,
                    MONTH
                )
        )
    )
        = MAX ( 'Table'[Data] ),
    1
)

     

     

    Then put the measure into visual level Filters and set Show items when the value is 1.

     

     

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    karinafreitass , why the date table is on the many side in this case. It should be on one side. Check and correct the join.

    With help time intelligence and date table, you can have

    example

     

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
    last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
    last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

     

     

    Power BI — Month on Month with or Without Time Intelligence
    https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
    https://www.youtube.com/watch?v=6LUBbvcxtKA

    • karinafreitass's avatar
      karinafreitass
      Icon for Helper III rankHelper III
      5 years ago

      amitchandak Thank you very much for your answer.

      I adjusted the relation between the tables.

      I created an image that better replicates what I want as a result

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi karinafreitass ,

     

    I made a simple sample.

    First of all, there can be no relationship between the two tables. If it is relevant, the value of the previous month cannot be obtained.

     

    Measure =
IF (
    CALCULATE (
        MAX ( 'Table'[Data] ),
        FILTER (
            ALL ( 'Table' ),
            [Data]
                = DATESINPERIOD (
                    'dCalendar'[Data],
                    SELECTEDVALUE ( dCalendar[Data] ),
                    -1,
                    MONTH
                )
        )
    )
        = MAX ( 'Table'[Data] ),
    1
)

     

     

    Then put the measure into visual level Filters and set Show items when the value is 1.

     

     

    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.