cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dslxco
Regular Visitor

Time Intelligence at Graph Interactions %MoM, %YoY

Hello to you all, come to ask for assistance.

Firstly, english isn't my mohter language, so i feel a bit confused if i'm being clear.

I'll try to give many details as possible.

 

I'm having some problems with time intlligence;

As it sees, I want to make the bar graph interactive with the
stampcards. As it shows in the pics.

dslxco_0-1637858037007.png

dslxco_1-1637858047792.png

Those are the dax formulas in use are

 

¹%Over90 MoM% = 
IF(
	ISFILTERED('PERFIL_RENEG'[DT_BASE]),
	ERROR("Medidas rápidas de inteligência de tempo somente podem ser agrupadas ou filtradas pela hierarquia de data fornecida pelo Power BI ou pela coluna de data primária."),
	VAR __PREV_MONTH = CALCULATE([¹%Over90], DATEADD('PERFIL_RENEG'[DT_BASE].[Date], -1, MONTH))
	RETURN
		DIVIDE([¹%Over90] - __PREV_MONTH, __PREV_MONTH)
)

 

¹%Over15-90 MoM% = 
IF(
	ISFILTERED('PERFIL_RENEG'[DT_BASE]),
	ERROR("Medidas rápidas de inteligência de tempo somente podem ser agrupadas ou filtradas pela hierarquia de data fornecida pelo Power BI ou pela coluna de data primária."),
	VAR __PREV_month = CALCULATE([¹%Over15-90], DATEADD('PERFIL_RENEG'[DT_BASE].[Date], -1, MONTH))
	RETURN
		DIVIDE([¹%Over15-90] - __PREV_month, __PREV_month)
)

 

¹AtvMoM = 
IF(
	ISFILTERED('PERFIL_RENEG'[DT_BASE]),
	ERROR("Medidas rápidas de inteligência de tempo somente podem ser agrupadas ou filtradas pela hierarquia de data fornecida pelo Power BI ou pela coluna de data primária."),
	VAR __PREV_MONTH =
		CALCULATE(
			SUM('PERFIL_RENEG'[Saldo Ativo]),
			DATEADD('PERFIL_RENEG'[DT_BASE].[Date], -1, MONTH)
		)
	RETURN
		DIVIDE(SUM('PERFIL_RENEG'[Saldo Ativo]) - __PREV_MONTH, __PREV_MONTH))
)

 

The reference table in use shows date by full, example: Thursday, November, 15, 2021.

4 REPLIES 4
v-chenwuz-msft
Community Support
Community Support

Hi @dslxco ,

 

The problem is the DATEADD(). When you select a month, the fact table imported to this page has been filtered only left the month you selected. the previous months have not been imported to the page. so there is no result.

 

Maybe you can use some measures like the following when on the condition you have selected one month.

 

VAR _s =
    SELECTEDVALUE( 'PERFIL_RENEG'[DT_BASE].[MonthNo] ) - 1
VAR __PREV_MONTH =
    CALCULATE(
        SUM( 'PERFIL_RENEG'[Saldo Ativo] ),
        FILTER( ALL( 'PERFIL_RENEG' ), MONTH( [DT_BASE] ) = _s )
    )
RETURN
__PREV_MONTH
when no selected, use your code is fine. combine these two conditions via if().
 

Best Regards

Community Support Team _ chenwu zhu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

lbendlin
Super User
Super User

What is the error message when you select a single column?

 

Be aware that your cards need to use a "Max(date)" filter otherwise the computation will spread across all bars - likely not what you want.

Oh, it shows those: (MdxScriptModel) (82, 2), (MdxScriptModel) (69, 2), (MdxScriptModel) (50, 2)

oooh, fancy.  Not sure if that is a bug or caused by the way your data model is wired.  Could you potentially provide a sample pbix?

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!