Forum Discussion

honeybee84's avatar
honeybee84
Frequent Visitor
1 year ago
Solved

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 perio...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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