Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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!
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |