Previous Month function Aggregation

[eyszyszki]

Hello, 
I am using previous month function and i've encountered a problem that this function does not work properly adding new aggregations like below:

 
Turns out that values are dissapearing once new aggregation is added. Does anyone have an idea how to solve such problem?

Thanks in advance!

[eyszyszki]

Funny and confusing is that DATEADD function shows the total in different way thay EDATE:

 

Anyone have an idea what is casuing such confusion?

[danextian · Accepted solution]

Hi @eyszyszki ,

It is a best practice to use a separate dates/calendar table as this simplifies time intelligence calculations. While PREVIOUSMONTH might work initially, adding granularity to a visual can make it stop working. You can make a separate dates table in DAX with CALENDAR function using specific dates or dates in your existing table

Dates =
CALENDAR ( DATE ( 2023, 1, 1 ), TODAY () )

Dates =
CALENDAR (
    MIN ( Filtered_table[Source.Name] ),
    MAX ( Filtered_table[Source.Name] )
)

Afer that, create a one to many relationship from Dates[Date] to Filtered_table[Source.Name]. Then modify your formula.

Cost Prev Month =
CALCULATE (
    SUM ( Filtered_table[Monthly Cost in DKK] ),
    PREVIOUSMONTH ( Dates[Date] )
)

Use the date column from the Dates table instead of the one from your fact table in the visuals.

Or you can try the formula below

=
CALCULATE (
    SUM ( Filtered_table[Monthly Cost in DKK] ),
    FILTER (
        ALL ( Filtered_table[Source.Name] ),
        Filtered_table[Source.Name] = EDATE ( MAX ( Filtered_table[Source.Name] ), - 1 )
    )
)