Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
karinafreitass
Helper III
Helper III

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

Screenshot (101)_LI.jpg

in table:

Screenshot (99)_LI.jpg

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

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

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.

Screenshot 2021-04-28 151510.png

 

 

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.

View solution in original post

3 REPLIES 3
v-stephen-msft
Community Support
Community Support

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.

Screenshot 2021-04-28 151510.png

 

 

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.

amitchandak
Super User
Super User

@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

@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

imagem.png

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors