Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello,
I have an issue that seems to be pretty simple but I'm not yet able to solve it. In PowerBi I have created a table that shows Forecast and the Spent values by month. I simple need a measure that check if the month in the month column is older than todays month, then take the value from the Spent column otherwise take it from the Forecast column. I appreciate any help.
Thanks
Olaf
Solved! Go to Solution.
Hi @OlafA69 ,
Here are the steps you can follow:
1. Create calculated table.
Table 2 =
var _table=
CALENDAR(
DATE(2023,1,1),DATE(2023,12,31))
return
ADDCOLUMNS(
_table,"Month",FORMAT([Date],"mmmm"))
2. Create measure.
Measure =
var _today=TODAY()
var _mindate=DATE(YEAR(_today),MONTH(_today),1)
var _maxdate=EOMONTH(_today,0)
var _date=
MAXX(FILTER(ALL('Table 2'),'Table 2'[Month]=MAX('Table'[Month])),'Table 2'[Date])
return
IF(
_date<_mindate,SUMX(FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])),[Spent]),
IF(
_date>_maxdate,SUMX(FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])),[Forecast])))
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @OlafA69 ,
Here are the steps you can follow:
1. Create calculated table.
Table 2 =
var _table=
CALENDAR(
DATE(2023,1,1),DATE(2023,12,31))
return
ADDCOLUMNS(
_table,"Month",FORMAT([Date],"mmmm"))
2. Create measure.
Measure =
var _today=TODAY()
var _mindate=DATE(YEAR(_today),MONTH(_today),1)
var _maxdate=EOMONTH(_today,0)
var _date=
MAXX(FILTER(ALL('Table 2'),'Table 2'[Month]=MAX('Table'[Month])),'Table 2'[Date])
return
IF(
_date<_mindate,SUMX(FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])),[Spent]),
IF(
_date>_maxdate,SUMX(FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])),[Forecast])))
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @OlafA69 check link / video from link below
https://www.sqlbi.com/articles/showing-actuals-and-forecasts-in-the-same-chart-with-power-bi/
Budet is similar like your forecast.
Hope this help, kudos appreciated.
Proud to be a Super User!
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
7 | |
7 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |