Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.

Reply
honeybee84
Frequent Visitor

Need help with DAX code for cumulative values

Hi 

 

I have fact table with [reporting period] and [month]. I have a date table which has an active relationship to [reporting period] and inactive relationship to [month]. 

 

My reporting period has Dec-24, Jan-25, Feb-25, Mar-25, etc.

 

My goal is to have measure to show value as [Prior Month Forecast]. If I filter by period, let's say Apr-25, it will filter by reporting period Mar-25 (M-1) and it will give the forecast values each month as CUMULATIVE. 

 

So far all the codes I have tried give me MONTHLY values, as opposed to CUMULATIVE. Any idea how to fix this?

 

This is the code that shows incorrect monthly values as opposed to cumulative.

 

Cumulative Prior Month Forecast =
VAR SelectedMonthDate = MAX(DIM_Date[FullDateAlternateKey])
VAR PriorReportingPeriodDate = DATE(YEAR(SelectedMonthDate), MONTH(SelectedMonthDate) - 1, 1)  // First day of prior month

RETURN
CALCULATE(
    SUMX(
        FILTER(
            ALL(DIM_Date),
            DIM_Date[Year] = YEAR(PriorReportingPeriodDate) &&
            DIM_Date[Month] <= MONTH(SelectedMonthDate)  // Ensures cumulative roll-up
        ),
        [Forecast]  // Accumulates forecast values month by month
    ),
    TREATAS(
        {PriorReportingPeriodDate}, FACT_CombinedData[Reporting Period]  // Passes filter without needing an active relationship
    )
)

 

Any guidance is greatly appreciated.

Thank you. 

1 ACCEPTED SOLUTION
v-bmanikante
Community Support
Community Support

Hi @honeybee84 ,

 

Thank you for reaching out to Microsoft Fabric Community Forum.

@johnt75 @bhanu_gautam Thank you for your quick inputs.

 

@honeybee84 Could you please try the below DAX measure.:

Cumulative Prior Month Forecast =
VAR SelectedReportingPeriod = MAX(DIM_Date[Reporting Period])
VAR PriorReportingPeriod =
CALCULATE(
MAX(DIM_Date[Reporting Period]),
DATEADD(DIM_Date[Reporting Period], -1, MONTH)
)
RETURN
CALCULATE(
[Forecast],
FILTER(
ALL(DIM_Date),
DIM_Date[Month] <= MAX(DIM_Date[Month]) 
),
TREATAS({PriorReportingPeriod}, FACT_CombinedData[Reporting Period]),
USERELATIONSHIP(DIM_Date[Month], FACT_CombinedData[Month]) 
)

 

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

 

Regards,

B Manikanteswara Reddy

 

View solution in original post

9 REPLIES 9
v-bmanikante
Community Support
Community Support

Hi @honeybee84 ,

 

Thank you for reaching out to Microsoft Fabric Community Forum.

@johnt75 @bhanu_gautam Thank you for your quick inputs.

 

@honeybee84 Could you please try the below DAX measure.:

Cumulative Prior Month Forecast =
VAR SelectedReportingPeriod = MAX(DIM_Date[Reporting Period])
VAR PriorReportingPeriod =
CALCULATE(
MAX(DIM_Date[Reporting Period]),
DATEADD(DIM_Date[Reporting Period], -1, MONTH)
)
RETURN
CALCULATE(
[Forecast],
FILTER(
ALL(DIM_Date),
DIM_Date[Month] <= MAX(DIM_Date[Month]) 
),
TREATAS({PriorReportingPeriod}, FACT_CombinedData[Reporting Period]),
USERELATIONSHIP(DIM_Date[Month], FACT_CombinedData[Month]) 
)

 

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

 

Regards,

B Manikanteswara Reddy

 

Hi @honeybee84 ,

 

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

 

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

 

Please don't forget to give a "Kudos vbmanikante_0-1748082099874.png" – I’d truly appreciate it!

 

Regards,

B Manikanteswara Reddy

Hi @honeybee84 ,

 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

 

Please don't forget to give a "Kudos vbmanikante_0-1748339363798.png" – I’d truly appreciate it!

 

Regards,

B Manikanteswara Reddy

Hi @honeybee84 ,

 

May I ask if you have gotten this issue resolved?

 

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

 

Please don't forget to give a "Kudos vbmanikante_0-1748608023156.png" – I’d truly appreciate it!

 

Regards,

B Manikanteswara Reddy

 

johnt75
Super User
Super User

You can try

Cumulative Prior Month Forecast =
VAR SelectedMonthDate =
    MAX ( DIM_Date[FullDateAlternateKey] )
VAR DatesToUse =
    CALCULATETABLE (
        DATESYTD ( DIM_Date[Year Month] ),
        TREATAS (
            { EOMONTH ( SelectedMonthDate, -2 ) + 1 },
            DIM_Date[FullDateAlternateKey]
        )
    )
VAR Result =
    SUMX ( DatesToUse, [Forecast] )
RETURN
    Result

where DIM_Date[Year month] is the date column linked to your fact reporting period column.

Hi @johnt75

Unfortunately it's still giving monthly values and values are also including prior reporting months as opposed to 1 month prior only. Thanks for replying though 🙂 

I'm not entirely sure what you're trying to get. Can you give a list of months along with the months which should be included in the calculation. e.g. March 2025 should include Jan 2025 and Feb 2025.

bhanu_gautam
Super User
Super User

@honeybee84 , Try using

Cumulative Prior Month Forecast =
VAR SelectedReportingPeriod = MAX(FACT_CombinedData[Reporting Period])
VAR PriorReportingPeriod = EDATE(SelectedReportingPeriod, -1) // Get the prior reporting period

RETURN
CALCULATE(
SUMX(
FILTER(
ALL(DIM_Date),
DIM_Date[FullDateAlternateKey] <= SelectedReportingPeriod &&
DIM_Date[FullDateAlternateKey] >= PriorReportingPeriod // Ensures cumulative roll-up from prior reporting period
),
[Forecast] // Accumulates forecast values month by month
),
TREATAS(
{PriorReportingPeriod}, FACT_CombinedData[Reporting Period] // Passes filter without needing an active relationship
)
)




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Hi @bhanu_gautam 

Unfortunately it's still giving monthly values. Thanks for replying though 🙂 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Kudoed Authors