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.
I have a table with amounts per month.
I'd like to create a new column to fill in the future months which are now blank (July 2020 onwards) by using the amount of the previous month (in this case using the amount of June 2020 but this has to change dinamically every month).
I've create the following measure to calculate the amount of the previous month but it will only populate 1 line (July 2020), I dont know how to make it populate all the remaining future months - any ideas?
My current formula is like this:
Prev Month Amount £ = CALCULATE(Sum(Transactions[Amount]),PREVIOUSMONTH('Calendar'[Date]))
Solved! Go to Solution.
Please try this expression
Prev Month = var __thismonth = SUM(Transaction[Amount])
var __maxdate = CALCULATE(MAX(Transaction[Date]), ALL('Date'))
var __start = DATE(Year(__maxdate), Month(__maxdate),1)
var __prevsales = CALCULATE(SUM(Transaction[Amount]), DATESBETWEEN('Date'[Date], __start, EOMONTH(__start,0)))
return IF(ISBLANK(__thismonth), __prevsales, __thismonth)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mq2020 , only for July
Prev Month Amount £ =
if(eomonth(max('Calendar'[Date]),1) = eomonth(today(),1),
CALCULATE(Sum(Transactions[Amount]),PREVIOUSMONTH('Calendar'[Date])), blank())
for future months try like
Future Month Data =
var _max = eomonth(maxx(Transactions,Transactions[Date]),0)
var _min = eomonth(_max,-1)+1
var _cal = calculate(Sum(Transactions[Amount]), filter(all('Calendar'[Date]), 'Calendar'[Date] >=_min && 'Calendar'[Date] <=_max))
return
if(eomonth(max('Calendar'[Date]),1) >= eomonth(today(),1),_cal,blank())
@amitchandak , thanks for your help.
I tried both formulas and this is what it returns.
Do I need to modify something in the future months formula so that it populates the rest of months?
Thanks
Hi,
Share the link from where i can download your PBI file.
Please try this expression
Prev Month = var __thismonth = SUM(Transaction[Amount])
var __maxdate = CALCULATE(MAX(Transaction[Date]), ALL('Date'))
var __start = DATE(Year(__maxdate), Month(__maxdate),1)
var __prevsales = CALCULATE(SUM(Transaction[Amount]), DATESBETWEEN('Date'[Date], __start, EOMONTH(__start,0)))
return IF(ISBLANK(__thismonth), __prevsales, __thismonth)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |