Issue calculating variation of a value

[EmanuelTavares]

Good morning,

I need to present a graphic with the sales variation month by month and I'm using the follow measure:

Variacao Mensal = 
IF(
	ISFILTERED(Pedidos[Data Criação]),
	ERROR("As medidas rápidas de análise de tempo só podem ser agrupadas ou filtradas pela hierarquia de data fornecida pelo Power BI ou pela coluna de datas primária."),
	VAR __PREV_MONTH = CALCULATE(SUM(ORDERS[total]), DATEADD(ORDERS[Created].[Date], -1, MONTH))
	RETURN DIVIDE(CALCULATE(SUM(ORDERS[total]), ORDERS[Created]) - __PREV_MONTH, __PREV_MONTH)
)

The calculation is fine, but when I put the measure in a graphic or table, it shows the "next month" with -100%.

Something like that:

I tried to redo the measure, filter the date, etc etc, but nothing works.

I verified the value of variable __PREV_MONTH using the follow measure:

PREVMONTH = 
	VAR __PREV_MONTH = CALCULATE(SUM(ORDERS[total]), DATEADD(Pedidos[Created].[Date], -1, MONTH))
    RETURN __PREV_MONTH

and, in fact, it returns a value for the month of July, that is the total sales of June. But June is the current month today.

Can anybody help me, please?

[mahoneypat · Accepted solution]

Would it work to add an IF(ISBLANK ... into your Return to check if one of your values is blank and return blank in that case?

Pat

[mahoneypat · Accepted solution]

Would it work to add an IF(ISBLANK ... into your Return to check if one of your values is blank and return blank in that case?

Pat

[EmanuelTavares · Accepted solution]

Hi @mahoneypat 

Thanks for the message.

Great idea!

I change the code to:

Variacao Mensal = 
IF(
	ISFILTERED(ORDERS[Created]),
	ERROR("As medidas rápidas de análise de tempo só podem ser agrupadas ou filtradas pela hierarquia de data fornecida pelo Power BI ou pela coluna de datas primária."),
	VAR __PREV_MONTH = CALCULATE(SUM(ORDERS[Created]), DATEADD(ORDERS[Created].[Date], -1, MONTH))
    VAR __CUR_MONTH = CALCULATE(SUM(ORDERS[total]), ORDERS[Created])
    VAR RESULT = IF(__CUR_MONTH, DIVIDE(__CUR_MONTH - __PREV_MONTH, __PREV_MONTH, 0))
	RETURN RESULT
)

and works very well.

Thanks again!