Showing %Change from Prior Month in a visual when the visual is filtered to show only current month

[ldwf]

I have a simple table visual that shows product attributes and a score.  The table is filtered to only show information for the current month, but I need to display a %Change between Current and Prior Month column as well.  I can't get it to work because I filter the visual for the current month, and the results are not correct.  Note that there is no date slicer on the report because the visual must always default to the maximum reporting period, but I need to show %change from prior month.  I created measures using DAX for the Current and Prior months values as: 

Current Month Value=

    VAR a=Calculate(SUMX(TABLE1,TABLE1[Score]),TABLE1[Current Reporting Period Ind="Y")

RETURN a

Prior Month Value=

        VAR a=Calculate(

           TABLE1(Score)

          TABLE1(Prior_Reporting_Period_Ind="Y"
)

RETURN a

and the MoM% as:

MoM % =

DIVIDE(

    [Score] - [Score-PRIOR Month Value],

    [Score-PRIOR Month Value],

    0

)

When I add te MoM% to the table, the value is always 0.  I'm guessing it is because of the fact that I filter the visual to where the Current Reporting Ind='Y'

[cengizhanarslan]

I assume you do not have proper date table in your scenario, if you had you could simply use PREVIOUSMONTH() for your calculations. Bu tin your case you could try using:

Current Month Value =
CALCULATE (
    SUM ( TABLE1[Score] ),
    TABLE1[Current_Reporting_Period_Ind] = "Y"
)

Prior Month Value =
CALCULATE (
    SUM ( TABLE1[Score] ),
    REMOVEFILTERS ( TABLE1[Current_Reporting_Period_Ind] ),
    TABLE1[Prior_Reporting_Period_Ind] = "Y"
)

MoM % =
DIVIDE (
    [Current Month Value] - [Prior Month Value],
    [Prior Month Value],
    0
)

[Kedar_Pande]

@ldwf 

Current Month Value =
CALCULATE(
SUMX(TABLE1, TABLE1[Score]),
TABLE1[Current Reporting Period Ind] = "Y"
)

Prior Month Value =
CALCULATE(
SUMX(TABLE1, TABLE1[Score]),
TABLE1[Prior Reporting Period Ind] = "Y",
REMOVEFILTERS(TABLE1) 
)

MoM % =
VAR Curr = [Current Month Value]
VAR Prior = [Prior Month Value]
RETURN
DIVIDE(Curr - Prior, Prior, 0)

Drop visual filter pane Current Ind="Y". Measures handle it dynamically.

[krishnakanth240]

Hi @ldwf 

Can you share sample data of table in text format.

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[parry2k · Accepted solution]

@ldwf yes you need to define what prior month is, it is actual based on the calendar or indicator, if it is by calendar month, as a best practice, add a date dimension in your model and use it for time intelligence calculations. Once the date dimension is added, mark it as a date table on the table tools. Check the related videos on my YT channel

Add Date Dimension
Importance of Date Dimension
Mark date dimension as a date table - why and how?
Time Intelligence Playlist

[parry2k]

@ldwf  try this measure, drop this measure in the visual to check the value it returns, then later you can remove it from the visual

Prior Month Value=

        VAR a=Calculate(

           TABLE1(Score)
           ALL ( Table1 ),
          TABLE1(Prior_Reporting_Period_Ind="Y"
)
RETURN a

[ldwf]

Actually, the Prior_Reporting_Period_Ind should not be used.  I defined it using the actual prior month whether there was a score or not, but many products may not have a value for the actual prior month, i.e, the prior month may be three months ago.  Hence, I should probably define this not using indicators