Trouble with DAX calculation

DAX DAX Calculations
1

Hi

I’m trying to develop some budget vs sales charts but am struggling to get a DAX calculation to work.

I want the Total Sales column to populate up to the date of last sale (in this data its Feb 4).

If there are no sales for any day up to Feb 4 (i.e. 28 - 30 Jan below), then it should show ‘0’.

For any day after the date of last sale (up to the end of the financial year - 30 June), Total Sales should be blank.

Thanks.

image

Forecasting sample.pbix (90.3 KB)

2

@KieftyKids ,

This should do the trick:

Sales with 0s = 

VAR MaxDate =  
CALCULATE(
    MAX( Sales[Date] ),
    REMOVEFILTERS( Dates[Date] )
)

VAR Result =
IF( SELECTEDVALUE( Dates[Date] ) <= MaxDate,
    COALESCE( [Total Sales], 0 ),
    BLANK()
)

RETURN Result

image
image817×963 94.5 KB

– Brian

3

Hi @BrianJ

Thank you!

I like COALESCE. Never seen it before.

A bit annoyed I couldn’t work this out myself. I guess its part of the learning process.

4

@KieftyKids ,

COALESCE is handy. It’s not at all necessary, since it’s basically just a shorthand IF statement, but it can make for more concise code, and it’s good for “style points”. :grinning:

  • Brian