Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Revising Formula for MoM %

Hi,

 

I would like to ask if is there a way to revise MoM% by excluding future month in the calculation?

 

As of writing, the month today August and PBI is already showing September. Please note that September is not yet part of the raw data.

 

Expectation is that PBI dashboard should only show months in the current and past periods.

 

 

Formula:

MoM% =

IF(

    ISFILTERED('Mail'[Created Date]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __PREV_MONTH =

        CALCULATE(

            DISTINCTCOUNT('Mail'[FILE ID]),

            DATEADD('Mail'[Created Date].[Date], -1, MONTH)

        )

    RETURN

        DIVIDE(DISTINCTCOUNT('Mail'[FILE ID]) - __PREV_MONTH, __PREV_MONTH)

)

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    HI Anonymous,

    Current dax expression are based on aggarede row context, you cna fix then to static result except you use static value as conditions.(power bi not include historical data feature)
    For this scenario, you can create a custom table with values that extract and reference in the dax expressions. The formula result will be fixed until you change on the parameter table.

    Regards,

    Xiaoxin Sheng

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    HI Anonymous,

    Current dax expression are based on aggarede row context, you cna fix then to static result except you use static value as conditions.(power bi not include historical data feature)
    For this scenario, you can create a custom table with values that extract and reference in the dax expressions. The formula result will be fixed until you change on the parameter table.

    Regards,

    Xiaoxin Sheng