Calculate previous months value when the previous month is missing

[gabitu]

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

[SachinNandanwar]

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]

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]

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]

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]

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,

[gabitu]

Thank you DataNinja777, appreciate your help with this! Unfortunatelly the new code returns only 0s, please see the screenshot below.

For your convenience I have attached a link with the pbix file: Event Changes TEST.pbix

[DataNinja777]

Hi @gabitu ,

Thank you for providing the additional information and the pbix file. Could you please confirm if the DAX formula below generates the desired output?

REVISION = 
VAR CurrentYearMonth = ma_event[YearMonth]
VAR NextYearMonth =
    CALCULATE(
        MIN(ma_event[YearMonth]),
        FILTER(
            ALL(ma_event),
            ma_event[YearMonth] > CurrentYearMonth
        )
    )
VAR RevisionValue =
    CALCULATE(
        SUM(ma_event[previous]),
        FILTER(
            ALL(ma_event),
            ma_event[YearMonth] = NextYearMonth
        )
    )
RETURN
    COALESCE(RevisionValue, 0)

I attach the pbix file for your reference.

Best regards,

[gabitu]

Thank you so much DataNinja777! This works better but I still have issues when the event date is happenning in the same month, please see the screenshot below. Basically the green line correspondence are correct but the red ones are not. Thanks again for helping me with this complex problem, really appreciate it!

[DataNinja777 · Accepted solution]

@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,

[gabitu]

Thank you so much, that it exactly how if should work!!! 😀

I appeciate your advice on the index column, however the only reliable column I can use as an index is the id column, this will ever increase as the number of events will increase. In this case only one type of event is retrieved from the database, that's the reason the ids are not continguous, but it might works as an index as they are naturally ascending ordered. 

Thank you so much for your help, this is really top notch coding , I can't express enough my gratitude and admiration for your help!

Many thanks,

Gabriel