Forum Discussion

KVPro's avatar
KVPro
Frequent Visitor
4 years ago

Last date value - lastnonblank doesn't working

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. 

6 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      KVPro
      Frequent Visitor
      4 years ago

      Sample of Data:

       

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      KVPro
      Frequent Visitor
      4 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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.