Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
5 years ago

Monthly Variation

Hello Community.

To calculate the monthly variation I use the following measure:

Var. CAPITAL PREVIOUS MONTH ?
VAR MesAnterior = CALCULATE([MontoCapital],DATEADD(Calendario[Fecha],-1,MONTH))
RETURN
DIVIDE(([MontoCapital]-MesAnterior),MesAnterior)
note. The measure Capital Amount - SUM(Sales[CAPITAL])

I have 2 doubts:

If filter for the year 2020 gives me the following:

CristianCL21_0-1620926930096.png

My problem is as follows, if filter for the year 2021, it gives me 2 problems:

1.- Does not calculate the monthly variation in January compared to December 2020 (review previous image where December 2020 IF you have data)

2.- Adds the Month of June 2021, where I have no data.

CristianCL21_1-1620927002536.png

Assaulted from now on.

CristianCL

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous,

    I think this should be related to the time intelligence function DATEADD. (its filter range has been defined internal and you can't use all function to ignore/custom the filter effects)

    For this scenario, I'd like to suggest you use date function to manually define the filter ranges and calculate, it should better than 'time intelligence' function and allow you to custom on them.

    Time Intelligence "The Hard Way" (TITHW)  

    Regards,

    Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous,

    Any update for this? Did these suggestions help? If not please share some more detailed information to help us clarify your scenario.
    Regards,
    Xiaoxin Sheng