cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
dcrios
Frequent Visitor

How to get a value of the same day from previous month?

Hello guys,

 

I have a problem that I wish to solve, if anyone could help. I tried to calculate a value of the same day, from previous month and I don't have a clue about how to do it. The comparation that I wish to show is the sales of 05-sep-2017 subtracting 05-aug-2017, in a line chart opened by day, for example. It wuold be something like this:

 

test = Calculate(SUM([Value]) ,  DATEADD([Date Event].[Day]))

Calculate(SUM([Value]) ,  DATEADD([Date Event].[Day], -1, month))

 

Can anybody help with a solution?

 

Thanks in advance

1 ACCEPTED SOLUTION
v-yulgu-msft
Microsoft
Microsoft

Hi @dcrios,

 

I created a sample table based on my assumption.

2.PNG

 

Please create measures like:

Last Month =
CALCULATE (
    SUM ( [Value] ),
    FILTER (
        ALL ( 'Month-Sales' ),
        MONTH ( 'Month-Sales'[Date Event] )
            = MONTH ( MAX ( 'Month-Sales'[Date Event] ) ) - 1
            && DAY ( 'Month-Sales'[Date Event] ) = DAY ( MAX ( 'Month-Sales'[Date Event] ) )
    )
)

difference = MAX('Month-Sales'[Value])-[Last Month]

1.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
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

5 REPLIES 5
v-yulgu-msft
Microsoft
Microsoft

Hi @dcrios,

 

I created a sample table based on my assumption.

2.PNG

 

Please create measures like:

Last Month =
CALCULATE (
    SUM ( [Value] ),
    FILTER (
        ALL ( 'Month-Sales' ),
        MONTH ( 'Month-Sales'[Date Event] )
            = MONTH ( MAX ( 'Month-Sales'[Date Event] ) ) - 1
            && DAY ( 'Month-Sales'[Date Event] ) = DAY ( MAX ( 'Month-Sales'[Date Event] ) )
    )
)

difference = MAX('Month-Sales'[Value])-[Last Month]

1.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Great work, thanx it solves my pblm too, can u please explain in step by step 

Ashish_Mathur
Super User
Super User

Hi,

 

Drag the date column to the visual filter and apply a criteria of October 5.  Then write this calculated field formula

 

=CALCULATE(SUM([Value]),PREVIOUSMONTH(Calendar[Date]))

You should have a calendar table with running dates.  There should be a relationship from the Date column of your source data table to the Date column of your calendar table.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur my calendar table has future dates as we have set targets till 2019. This seems not to work .

Hi,

 

Please describe your question in detail.  Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors