Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
This works and returns the correct 'current month' values:
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))
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
@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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |