calculate dax if previous value is blank

[Anonymous]

Dear all...

need some help with dax.

the column is a accumative total and i need to calculate the no. of sales per month.

the blank value indicate the days when sales is closed.

how do i calculate the difference if the previous value is blank.

there could be 2 or more blank cell.

Thank you so much.

[Washivale]

Hi @Anonymous ,

You might need to add more details on what exactly you are trying to do with this data, tried downloading file from the link with no luck.

per your screenshot, if you just need number of sales per month, a simple calculate function as below should be sufficient.

= Calculate(Count(SalesIdCol), commulativevaluecol <>blank())

Thanks,

Washivale

[Anonymous]

@Washivale 

hihi..

i need the no. of sales per month but because there are blank value, so i need the formula to take the previous value...

there are times when there are more than 2 blank value.

[Washivale]

Hi @Anonymous ,

still getting an error message on gofile, i tried to reproduce a table from your screenshot, and created a measure as below

EndResults = VAR lrv = SELECTEDVALUE(Query1[Value])

Return

if(lrv=blank(),0, lrv-CALCULATE(

    LASTNONBLANK('Query1'[Value],1),

    FILTER(

        ALLSELECTED('Query1'[Date]),

        ISONORAFTER('Query1'[Date], MAX('Query1'[Date])-1, DESC)

    )

))

check if it helps.

Regards,

Washivale

[dax · Accepted solution]

Hi Washivale , 

You also could refer to my sample to see whether it work or not.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.