Compare two mesures

[JFaucher]

Hi,

I want to compare two measurements to determine whether the previous one is greater than the next one. This works, but the problem is that I have extra rows appearing in my table because I am comparing data from the previous month. How can I prevent my column from adding extra rows?

Regardss

[hugo_andrade]

Hi @JFaucher ,

Did you try to hide null values on the dimension?

Could you send a screenshot?

[hugo_andrade]

Also, could you please share the Dimension, and Measures used?

Thanks

[JFaucher]

Hi,

This is the expression of my measure : 

If(
  Sum({$<DateFinMois = {"$(=Max(DateFinMois))"}>} MontantImpayesCompte)
  >
  Sum({$<DateFinMois = {"$(=MonthEnd(AddMonths(Max(DateFinMois), -1)))"}>} MontantImpayesCompte),
  '▲',
  If(
    Sum({$<DateFinMois = {"$(=Max(DateFinMois))"}>} MontantImpayesCompte)
    <
    Sum({$<DateFinMois = {"$(=MonthEnd(AddMonths(Max(DateFinMois), -1)))"}>} MontantImpayesCompte),
    '▼',
    '-'
  )
)

I can't hide null values because i need it

Regards,

[hugo_andrade]

Hi @JFaucher ,
Got it. The “extra rows” show up because your measure pulls values from the previous month, which brings in dimension values that might not exist in the current month.

What is the dimension used on that chart?

Would you like to compare only for the same entities that exist in both months?

Before doing the ▲/▼, first check if the current-month sum is zero/absent; if so, return Null(). That way the column won’t materialize rows that only exist in the prior month.

[JFaucher]

Hi @hugo_andrade 

It's not so much a question of whether the entity exists or not, but rather of comparing an unpaid amount between the current month and the previous month. That's what I do in my summary. The problem is that I have other columns containing dimensions, and this creates rows because in the previous month the values in these columns were different from those in the current month.

[Amit_Prajapati]

Hi @JFaucher  ,If possible, please share a sample output or your expected result so we can get a clearer understanding of what you're aiming for.

[JFaucher]

Hi @Amit_Prajapati 

I have :

Name	Number	Date	Unpaid	Comparaison between m and m - 1
Dupont	1	Date1	100	Up
Dupont	1	Date2	0	Down

First line is the current month and the second line appears because on my last column i compare current month with the last month

And I want :

Name	Number	Date	Unpaid	Comparaison between m and m - 1
Dupont	1	Date1	100	Up