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
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
)
)
Hi bhanu_gautam
Unfortunately it's still giving monthly values. Thanks for replying though 🙂
