Forum Discussion

gabitu's avatar
gabitu
Frequent Visitor
1 year ago
Solved

Calculate previous months value when the previous month is missing

Hello Community,

 

I need to calculate the Revision values for the last month based on the previous value of the next month and it works very well as long as there's no month gaps in the date column. When the date is missing (for example we have no events on August 2013,  November 2013, August 2014, etc.) there are blanks in the REVISION columns and the REVISION is being summarized (please see the screenshot attached). How I can calculate the value of REVISION when I don't have contiguous months and some events are happening in the same month?

 

 

Many thanks!

 

Gabriel

  • DataNinja777's avatar
    DataNinja777
    1 year ago

    gabitu ,

     

    Thank you for the detailed demonstration. It seems that your goal is simply to shift the numbers in the previous column up by one row. In that case, the DAX formula below will achieve the desired result.

    Shifted Previous = 
VAR CurrentDate = 'ma_event'[date]
VAR ShiftedValue = 
    CALCULATE(
        MAX('ma_event'[previous]),
        FILTER(
            'ma_event',
            'ma_event'[date] = 
                CALCULATE(
                    MIN('ma_event'[date]),
                    FILTER('ma_event', 'ma_event'[date] > CurrentDate)
                )
        )
    )
RETURN 
    COALESCE(ShiftedValue, 0)

    This formula ensures that the previous value from the next date in the sequence is shifted to the current row.

     

    This formula does not rely on an index column, as it works directly based on the date column. However, if your data grows over time and contains duplicate dates, the row order may not be guaranteed. 

    To guarantee the row order, you can add an index column using Power Query. However, I couldn't modify your Power BI file through Edit Query due to insufficient permissions.

     

    I have attached an example pbix file for your reference. Please let me know if it meets your requirements.

     

    Best regards,

     

10 Replies

  • SachinNandanwar's avatar
    SachinNandanwar
    Icon for Impactful Individual rankImpactful Individual
    1 year ago

    I think you should use a measure instead of a calculated column given the non contigous rows of data that you have. If you dont want to then the solution provided by  DataNinja777 is the best

     

     

    MEASURE = 
VAR _Win =
    CALCULATE (
        MIN ( ma_event[date].[Date] ),
        WINDOW (
            -1,
            -1,
            ALL(ma_event),
            ORDERBY ( ma_event[date], DESC )
        )
    )
RETURN
    MINX (
        FILTER ( ALL ( ma_event ), ma_event[date].[Date] = _Win ),
        ma_event[previous]
    )

     

     

     

    • gabitu's avatar
      gabitu
      Frequent Visitor
      1 year ago

      Thanks Sachin, your proposed code works fine as well! For now I'll use DataNinja777 solution as the row order is guaranteed (i.e. the dates are ordered ascending and any new events will be added in the ascending order as well).

  • DataNinja777's avatar
    DataNinja777
    Icon for Super User rankSuper User
    1 year ago

    Hi gabitu ,

    To handle non-contiguous months while calculating the revision value, you can use DAX functions like PARALLELPERIOD, NEXTDATE, or LOOKUPVALUE to fetch the previous value from the next available month. Here's a revised approach that ensures the calculation works, even when some months are missing.

    REVISION = 
VAR NextMonthDate = 
    MINX(
        FILTER(
            dimDate,
            dimDate[Date] > MAX(dimDate[Date])
        ),
        dimDate[Date]
    )
RETURN
    CALCULATE(
        SUM(ma_event[previous]),
        FILTER(
            ALL(dimDate),
            dimDate[Date] = NextMonthDate
        )
    )

    With this revised measure, your REVISION calculation will:

    • Work even when there are gaps in the months.
    • Sum the appropriate values from the next available month without returning blanks.
    • Handle multiple events per month correctly by summing them as needed.

    Let me know if this resolves the issue or if you need further adjustments!

     

    Best regards,

    • gabitu's avatar
      gabitu
      Frequent Visitor
      1 year ago

      Thank you DataNinja777! Love your elegant solution, I have implemented your DAX code, however I got this error "A circular dependency was detected: ma_event[REVISION]"

      • DataNinja777's avatar
        DataNinja777
        Icon for Super User rankSuper User
        1 year ago

        Hi gabitu ,

         

        Thank you for your kind words! To avoid the circular dependency and ensure the measure aligns with your goal (handling non-contiguous months, summing multiple events), here’s an alternative approach:

        REVISION = 
VAR CurrentDate = MAX(dimDate[Date])
VAR NextAvailableDate = 
    CALCULATE(
        MIN(dimDate[Date]),
        FILTER(
            ALL(dimDate),
            dimDate[Date] > CurrentDate
        )
    )
RETURN
    IF(
        NOT(ISBLANK(NextAvailableDate)),
        CALCULATE(
            SUM(ma_event[previous]),
            dimDate[Date] = NextAvailableDate
        ),
        0
    )

        The logic only sums the previous values and does not create self-references within the REVISION measure

         

        This version should eliminate the circular dependency error. Let me know if it works or if you encounter any other issues!

         

        Best regards,