Previous Month Error using a Measure

[SpaceCat42]

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))

[v-lili6-msft]

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

Result:

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

[rajendran]

---

@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

[SpaceCat42]

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

[v-lili6-msft]

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

[SpaceCat42]

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.