Calculating Month to Date without using an external date filter

DAX DAX Calculations
1

Hi

I have a month to date calculation of:

Income to Date This Month =
CALCULATE ( [Total Sales], DATESMTD( Dates[Date] ) )

I don’t have a date filter on the page because of other calculations on the page. Therefore the MTD formula above is showing (blank) when placed in a card.

Is there a way to add a time intelligence formula to the measure so that it dynamically looks at the month based on today’s date? (i.e. today is July 2 so the measure will return yesterday’s sales plus today’s sales.)

Thanks

1 Like
2

Hi @KieftyKids,

Give this a go.

Custom MTD = 
VAR _MTD = 
    CALCULATETABLE( 
        VALUES( Dates[Date] ), 
        Dates[MonthOfYear] = MONTH( TODAY() ) && 
        Dates[Year] = YEAR( TODAY() ) 
    )
RETURN

CALCULATE( [Total Sales],
    _MTD
)

Or if you do have future dates/values, try something like this

Custom MTD v2 = 
VAR _Today = TODAY()
VAR _FirstDay = DATE( YEAR( _Today), MONTH( _Today), 1)
VAR _MTD = 
    CALCULATETABLE( 
        VALUES( Dates[Date] ), 
        Dates[Date] >= _FirstDay && 
        Dates[Date] <= _Today
    )
RETURN

CALCULATE( [Total Sales],
    _MTD
)

I hope this is helpful else please provide a sample

1 Like
3

Hi Melissa

Thank you. Option 1 worked perfectly.

Is it possible to adapt either option above to calculate Sales to Date Last Month or Sales to Date Last Year (with year being from 1 July 2020 )?

1 Like
4

Hi @KieftyKids,

I have no doubt but can you supply a sample PBIX that includes your Dates table.
Thanks!

1 Like
5

KieftyKids Sample.pbix (6.7 MB)

Hi Melissa

Thank you. I’m trying to get all the measures in the sample to calculate correctly without using a date filter.

The financial year starts 1 July.

I have been using this report for six months. It’s only when the new financial year started that I realised my Income to Date Last Year was incorrect.

My Income to Date this Month & This Year both appear to be giving the correct results.

Not so the other two measures.

By the way, this sample has the dynamic date table as shown in your Power Query mini series.

1 Like
6

@KieftyKids ,

> By the way, this sample has the dynamic date table as shown in your Power Query mini series.

:+1:

1 Like
7

Hi @KieftyKids,

Thanks that helps :wink:. Give this a go.

Income MTD = 
CALCULATE( [Total Sales],
    Dates[IsCurrentFP] = TRUE()
)

.

Income YTD = 
VAR _YTD = 
    CALCULATETABLE( 
        VALUES( Dates[Date] ),
        Dates[FiscalYearOffset] = 0,
        Dates[IsAfterToday] = FALSE()
    )
RETURN

CALCULATE( [Total Sales],
    _YTD
)

.

Income LM TD = 
VAR _LMTD = 
    CALCULATETABLE( 
        VALUES( Dates[Date] ),
        Dates[MonthOffset] = -1,
        Dates[DayOfMonth] <= DAY( TODAY() )
    )
RETURN

CALCULATE( [Total Sales],
    _LMTD
)

.

Income PYTD = 
VAR _TD = DATE(YEAR( TODAY())-1, MONTH(TODAY()), DAY(TODAY())) 
VAR _PYTD = 
    CALCULATETABLE( 
        VALUES( Dates[Date] ),
        Dates[FiscalYearOffset] = -1,
        Dates[Date] <= _TD
    )
RETURN
CALCULATE( [Total Sales],
    _PYTD
)
2 Likes
8

Wow. I didn’t realise how powerful the extended date table is.

Thank you.

This is the best website ever.

3 Likes