Forum Discussion
Need help with DAX code for cumulative values
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
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.
