EOMONTH not returning some values

[pamsardinha]

Hi!

I have two measures I created to calculate the difference of Saldo between months.

First one is calculating the saldo of end of month:

SaldoEOM = 
VAR Latest_Date = MAX('Table1'[Date])
RETURN
    CALCULATE (SUM('Table1'[Saldo]), 'Table1'[Date] = Latest_Date)

Second one is getting the saldo of end of month from previous month:

SaldoEPM =
CALCULATE(SUM('Table1'[Saldo]),
        'Table1'[Date] = EOMONTH(max('Table1'[Date]),-1),
        ALLEXCEPT('Table1', 'Table1'[Saldo], 'Table1'[Date], 'Table2'[System],  'Table3'[variable1],'Table4'[variable1]))

Third one is calculating the difference between them:

SaldoChange = [SaldoEOM] - [SaldoEPM]

The result looks like this:

Year	Month	SaldoEPM	SaldoEOM	SaldoChange	System
2023	Jan	0	10	10	A
2023	Feb	10	20	10	A
2023	Mar	20	12	-8	A
2023	Apr	12	15	3	A
2023	May	15	20	5	A
2023	Jun	20	50	30	A
2023	Jan	0	65	65	O
2023	Feb	65	42	-23	O
2023	Mar	42	95	53	O
2023	Apr	95	56	-39	O
2023	May	56	52	-4	O
2023	Jun		50	50	O

The problem is the SaldoEPM in the last line (Jun) for system "O" is not getting the value 52, as it should (like for system "A" 20).

I appreciate the help!

[Ashish_Mathur]

Hi,

Try this approach

1. Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number
2. Create a relationship (Many to One and Single) from the Date column of Table1 to the Date column of the Calendar Table
3. To your visual, drag Year and Month name from the Calendar Table
4. Write these measures

Total = sum('Table1'[Saldo])

Total in pm = calculate([total],previousmonth(calendar[date]))

Variance = [Total in pm]-[Total]

Hope this helps.

[pamsardinha]

Hi @Ashish_Mathur 

Thank you for your reply. Unfortunately I cannot create Calendar table as I am working with PowerBI datasets and I do not have the rights to create them.
In any case, I appreciate your input, but if you have any other suggestions, that will be awesome as well.
Thank you,
Pamela.

[v-xinruzhu-msft]

Hi @pamsardinha 

1. Please check if there is data for the date column 5/31 in the "o" type in your table, the current return is empty may have no data for the date column 5/31 last month, or there is no number 5/31 in the date column.

2.You can try the following measure

SaldoEPM =
CALCULATE (
    SUM ( 'Table1'[Saldo] ),
    FILTER (
        ALLSELECTED ( 'Table1' ),
        'Table1'[Date] = EOMONTH ( MAX ( 'Table1'[Date] ), -1 )
            && [System] IN VALUES ( 'Table2'[System] )
    )
)

Best Regards!

Yolo Zhu

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

[pamsardinha]

Hi @v-xinruzhu-msft 

Thank you for your reply.

I tried with your measure and it returns the same error...
I am sure that there is value at end of 31/5 because I can see in the calculate column SaldoEOM (in my example is 52 for system O).
If you have any other suggestion, I appreciate.

Thank you,

Pamela.