Show value as 0 before certain date otherwise show value based on filters applied

[Anonymous]

Hi Guys,

Seeking help solving a DAX problem.

I am trying to create a DAX measure which would set all values before current date to 0 otherwise show the expected value.

CPU forecast_WIP =

VAR currentdate = CALCULATE(MAX(Historical_allocation_total[DATE]),Historical_allocation_total[DATE] = TODAY())

RETURN VALUE(IF(ALLSELECTED(Historical_allocation_total[DATE]) < currentdate,0,

Historical_allocation_total[CPU_forecast] + calculate(Historical_allocation_total[CPU consumed up until forecast],ALL(Historical_allocation_total[DATE]))))

This does not work. I get an error = a table of multiple values was supplied where a single value was expected.

How should I structure this DAX logic to make sense?
Basically on this graph I want the yellow line to start where the pink one is ending. That part is okay, but the first part I dont want to be there.

The other 2 measure defined in the previous one consist of CPU_forecast which is just a cumulative sum by date and this one to determine the sum today which I am then adding to the forecast, so the forecast starts at a value where todays data end.

CPU consumed up until forecast =

CALCULATE(

    SUM('Historical_allocation_total'[CPU consumed (GHz)]),

    ALL(Historical_allocation_total),FILTER(Historical_allocation_total,Historical_allocation_total[DATE] = TODAY()))

Any help please?

Thanks

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please update the formula of measure [CPU consumed up until forecast] and [CPU forecast_WIP] as below and check if you can get the expected result...

CPU consumed up until forecast =
VAR _seldate =
    SELECTEDVALUE ( Historical_allocation_total[DATE] )
RETURN
    CALCULATE (
        SUM ( 'Historical_allocation_total'[CPU consumed (GHz)] ),
        FILTER (
            ALLSELECTED ( Historical_allocation_total ),
            Historical_allocation_total[DATE] >= TODAY ()
                && Historical_allocation_total[DATE] <= _seldate
        )
    )

CPU forecast_WIP =
IF (
    SELECTEDVALUE ( Historical_allocation_total[DATE] ) < TODAY (),
    0,
    Historical_allocation_total[CPU_forecast] + Historical_allocation_total[CPU consumed up until forecast]
)

If the above one is not working, please provide some sample data in the table 'Historical_allocation_total' with Text format and the calculation logic [CPU forecast (GHz)], [CPU consume(GHz)] and [CPU consumed up until forecast] with the special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Anonymous]

This is working exactly as I wanted! Thank you very much for such a swift help. Appreciate it 🙂

[amitchandak]

@Anonymous , Simply Try like

CALCULATE(MAX(Historical_allocation_total[DATE]),Historical_allocation_total[DATE] = TODAY()) +0

or

SUM('Historical_allocation_total'[CPU consumed (GHz)]) +0