Forum Discussion
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
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
RajHere'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.
Hi Raj, see my screen capture below. I need to use a measure otherwise i get a 'circular reference' error.
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
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
