Current Month vs Previous Month Comparison at Each day level

DAX DAX Calculations
4

@Vishy,

Not a problem – we just need to modify both measures to add a bit more time intelligence.

For the current month measure, it now calculates the first day and last day of the current month, and filters the total sales calculation for the dates within that range:

Sales CM =

VAR CurrentMonth = MONTH( TODAY() )
VAR CurrentYear = YEAR( TODAY() )
VAR LastDay = EOMONTH( TODAY(), 0 )
VAR FirstDay = DATE( CurrentYear, CurrentMonth, 1 )

RETURN
CALCULATE(
    [Total Sales],
    FILTER(
        Dates,
        Dates[Date] >= FirstDay &&
        Dates[Date] <= LastDay
    ),
    ALLEXCEPT( 
        Dates,
        Dates[Date]
    )
)

Similar approach for the previous month measure, but in this case we calculate the date range in a one column virtual table and then run the DATEADD function against that table for evaluation context:

Sales PM to CM = 

VAR CurrentMonth = MONTH( TODAY() )
VAR CurrentYear = YEAR( TODAY() )
VAR LastDay = EOMONTH( TODAY(), 0 )
VAR FirstDay = DATE( CurrentYear, CurrentMonth, 1 )

VAR ContextTable =
CALCULATETABLE(
    VALUES(Dates[Date]),
    Filter(
        Dates,
        Dates[Date] >= FirstDay &&
        Dates[Date] <= LastDay
    )
)

RETURN
CALCULATE(
   [Total Sales],
    DATEADD(
        ContextTable,
        -1,
        MONTH
    )
)

And here it is put together, this time with no slicer:

image
image1256×707 59.1 KB

Hope that’s helpful. Full solution file attached below.

P.S. Alternatively, you can handle the bulk of the time intelligence work in Power Query. @Melissa has a cool calendar offset method she uses for this sort of problem:

1 Like