Measure to

[OlafA69]

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

[v-yangliu-msft · Accepted 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

[some_bih]

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.