Tabular Editor (Previous Months measure sometimes is the total of previous months)

[Anonymous]

Hello friends, I'm using Tabular Editor to build a calculation group for Previous Month (later MOM, YOY etc), 

But when I tried it in the report, it shows like this:

It looks like sometimes the PreviousMonth data is a total of couple of months. 😣

Below is my calcaulation group and calcaulation items:

This is the Expression for Current and PreviousMonth:

• Current Month: 

SelectedMeasure ()

• Previous Month:

CALCULATE ( SELECTEDMEASURE (), DATEADD ( 'dimdate'[FiscalDate], -1, MONTH ) )

below is how the date date looks like:

Which part did I do wrong?

Thank you 🤔

[OwenAuger · Accepted solution]

Hello @ANC2527

The reason for this behavior appears to be related to how your Periods are defined.

Since there are 8 per year, do I take that each Period is approximately 45 days?

This means that subtracting one month from the dates of a Period using DATEADD will not be the same as changing the dates by a Period. Depending on the dates on which the values appear, you may see the values of several periods included when you change the dates earlier by one month.

I think you'll need to write DAX to capture your requirements instead of using DATEADD.

If you create a column in the dimdate table named Year Period Number that acts as a Year/Period index, so that it is incremented by one for each period, you could write code similar to the sales PM measure in the Month-to-Month Growth section of this article:

https://www.daxpatterns.com/month-related-calculations/

For the PreviousMeh calculation element, it would look something like:

VAR CurrentYearPeriodNumber = SELECTEDVALUE ( 'dimdate'[Year Period Number] )
VAR PreviousYearPeriodNumber = CurrentYearPeriodNumber - 1
VAR Result =
    CALCULATE (
        SELECTEDMEASURE (),
        REMOVEFILTERS ( 'dimdate' ),
        'dimdate'[Year Period Number] = PreviousYearPeriodNumber
    )
RETURN
    Result

Best regards

Owen