Forum Discussion

ingpiolo's avatar
ingpiolo
Frequent Visitor
2 years ago

calculation using Previous value empty

Hi everyone,

I have a table like this:

 

I should calculate subsequent fields as indicated iteratively by associating a measure (Filled fields are final values while empty ones are an estimate of future values based on the selection of a parameter).

Do you have any suggestions?

Many thanks

6 Replies

  • JoeBarry's avatar
    JoeBarry
    Solution Sage
    2 years ago

    Hi ingpiolo 

     

    To get the previous months data try this 

    Previous Month = CALCULATE(SUM('Table'[Valore]), DATEADD('Table'[Date], -1, MONTH))

     

    I would think then that you should create a new measure 

    [Previous Month] + [YourMeasure]

     

     

     

    Thanks

    Joe

     

    If this post helps, then please Accept it as the solution

     

     

    • ingpiolo's avatar
      ingpiolo
      Frequent Visitor
      2 years ago

      Thanks Joe for the reply.
      The problem is that I have empty cells and therefore with the measure
      Previous Month = CALCULATE(SUM('Table'[Value]), DATEADD('Table'[Date], -1, MONTH))

      I get correctly only the next first value referring to 01/07/2023.
      Subsequent ones should resume the value at 01/07/2023 which is saved as a measure. This is the result I would like to get

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi ingpiolo ,

        I created a sample pbix file(see the attachment), please find the details in it.

        Result = 
VAR _seldate =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _maxdate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Sum of value] > 0 )
    )
VAR _value =
    SUMX (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _maxdate ),
        [Sum of value]
    )
VAR _measures =
    CALCULATE (
        SUM ( 'Table'[Measure] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] <= _seldate )
    )
RETURN
    IF (
        ISBLANK ( SELECTEDVALUE ( 'Table'[Measure] ) ),
        BLANK (),
        _value + _measures
    )

        Best Regards