Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Measure to

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

 

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi  Anonymous ,

     

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi  Anonymous ,

     

    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