topic Re: Show value as 0 before certain date otherwise show value based on filters applied in DAX Commands and Tips

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

Anonymous — Wed, 09 Nov 2022 22:57:28 GMT

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

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

amitchandak — Thu, 10 Nov 2022 02:21:39 GMT

Anonymous , Simply Try like

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

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

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

Anonymous — Thu, 10 Nov 2022 08:09:53 GMT

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

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

Anonymous — Thu, 10 Nov 2022 09:05:04 GMT

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