Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

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
DAX
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

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