Revising Formula for MoM %

[Anonymous]

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)

)

[Anonymous · Accepted solution]

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