Forum Discussion
EOMONTH not returning some values
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!
4 Replies
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.
Hi Anonymous
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.
Hi,
Try this approach
- Create a Calendar Table with calculated column formulas for Year, Month name and Month number. Sort the Month name by the Month number
- Create a relationship (Many to One and Single) from the Date column of Table1 to the Date column of the Calendar Table
- To your visual, drag Year and Month name from the Calendar Table
- Write these measures
Total = sum('Table1'[Saldo])
Total in pm = calculate([total],previousmonth(calendar[date]))
Variance = [Total in pm]-[Total]
Hope this helps.
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.
