cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SpaceCat42 Frequent Visitor
Frequent Visitor

Previous Month Error using a Measure

This works and returns the correct 'current month' values:

  • Current Month Value = CALCULATE(SUM(Table1[Value]),MONTH(Table1[Originated])=MONTH(TODAY()))

 

Previous Month formula calculates every row with the correct amount. However i only want the row to populate if it mets the criteria of a "prevous month." Is there a simple fix here?

 

previous month = var current_month = MONTH(TODAY()) return CALCULATE(SUM('Table1'[Value]),FILTER('Table1',MONTH('Table1'[Originated])=current_month-1))

5 REPLIES 5
rajendran Super Contributor
Super Contributor

Re: Previous Month Error using a Measure


@SpaceCat42 wrote:

This works and returns the correct 'current month' values:

  • Current Month Value = CALCULATE(SUM(Table1[Value]),MONTH(Table1[Originated])=MONTH(TODAY()))

 

Previous Month formula calculates every row with the correct amount. However i only want the row to populate if it mets the criteria of a "prevous month." Is there a simple fix here?

 

previous month = var current_month = MONTH(TODAY()) return CALCULATE(SUM('Table1'[Value]),FILTER('Table1',MONTH('Table1'[Originated])=current_month-1))


Bit confusing here. As per the blue highlight, your previous month measure works fine. What is your requirement ?

 

Thanks
Raj

Community Support Team
Community Support Team

Re: Previous Month Error using a Measure

hi, @SpaceCat42

After my test, this formula works well,

if you want this new measure as below:

new previous month = CALCULATE(SUM(Table1[Value]),PREVIOUSMONTH(Table1[Originated].[Date]),MONTH(Table1[Originated])=MONTH(TODAY())-1)

For example:

basic data

4.PNG

Result:

5.PNG

here is pbix, please try it.

https://www.dropbox.com/s/mmccx2bj8bp470s/Previous%20Month%20Error%20using%20a%20Measure.pbix?dl=0

 

 

If it is not your case, please share some data sample and expected output . You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

 

 

Best Regards,

Lin

 

 

 

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

Re: Previous Month Error using a Measure

Demo.JPG

Here's  a screen grab. Current Month colum is correct. The previous month column has the proper value, but its replicated itself across all the data rows.

SpaceCat42 Frequent Visitor
Frequent Visitor

Re: Previous Month Error using a Measure

Hi Raj, see my screen capture below. I need to use a measure otherwise i get a 'circular reference' error.

Community Support Team
Community Support Team

Re: Previous Month Error using a Measure

hi, @SpaceCat42

These two formulas work well for create the measure

and do not use them to create the column.

 

Reference:https://www.sqlbi.com/articles/understanding-circular-dependencies/

 

Best Regards,

Lin

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 80 members 1,310 guests
Please welcome our newest community members: