Last date value - lastnonblank doesn't working

[KVPro]

Hello everyone

I have a big trouble with dax. I have some data with some blank rows for some months. If the value for month is blank i would like to get a value from previous time (month, week etc.)

My data is shown like this:

My local max means max value for month, If I have data for Sept (9 month nr) 2020-09-12 and 2020-09-25, max shows me only 2020-09-25.

I don't know to do this for month, weeks etc. 

These are my attempts, I suppose, that I will be use only months as context, but I really want to use something else for dynamic choosing months, weeks, quarters etc.

Attempt 1:

Attempt1 =

If(

'FactTable'[Local Max] = BLANK(),

CALCULATE([Local Max],PREVIOUSMONTH('Calendar'[Date])),

'FactTable'[Local Max]

)

Result:

If I have more than 1 month blanks, then I got empty value for example in 2020-11

Attemp2 -> I want to get something simillar to before attempt, but it is still incorrect.

Attempt2 =

IF(

[Attempt1] = BLANK(),

CALCULATE([Attempt1],PREVIOUSMONTH('Calendar'[Date])),

[Attempt1]

)

Result:

Yes, I have data in correct form, but I am afraid, that If I will have more than 2 months of blank values, it will be incorrect.

So, I have tried use lastnonblank, but it is still incorrect :(:

Code:

Lastnonblank doesnt working =

If(

'FactTable'[Local Max] = BLANK(),

LASTNONBLANK('Calendar'[Date],[Local Max]),

[Local Max])

Result:

Please help me, I have to do asap. 

[VahidDM]

Hi @KVPro 

As @lbendlin mentioned your post does not have enough information to go on;

But if I got your point correctly, try this code to add a new column to your table:

Column = 
VAR _A =
    CALCULATE (
        MAX ( 'Table'[Month nr] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Local max] <> BLANK ()
                && 'Table'[Year] = EARLIER ( 'Table'[Year] )
                && 'Table'[Month nr] < EARLIER ( 'Table'[Month nr] )
        )
    )
VAR _B =
    CALCULATE (
        MAX ( 'Table'[Local max] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Year] = EARLIER ( 'Table'[Year] )
                && 'Table'[Month nr] = _A
        )
    )
RETURN
    IF ( ISBLANK ( [Local max] ), _B, [Local max] )

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!

LinkedIn: www.linkedin.com/in/vahid-dm/

[KVPro]

It is incorrect in more than one products, these is my sample of Data

I made it in real data by crossjoin products and SortID, I have a lot of products, so It is very very difficult to do

[v-stephen-msft]

Hi @KVPro ,

It's easier to achieve in Power Query Editor.

1.Right click Product column, select group by. In operation, select All Rows.

2.Add a custom column.

= Table.FillDown([Count],{"Real Values"})

3.Remove the Count column and expand the Custom column.

4.Replace null with 0.

Best Regards,

Stephen Tao

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

[KVPro]

Hi

Table has been created crossjoin some tables in DAX, so I cannot do it in PowerQuery and I have to use dax functions 😞

[lbendlin]

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.

[KVPro]

Sample of Data: