Calculate SUM of Sales from Start of Month to Selected Date in Slicer

[Adam01]

Hi All!

I am trying to implement a Measure that will dynamically SUM a column based on the Datesbetween from the start of the month to the selected date.

So for example if I selected 14/03/2021 as a random date in my slicer from my Calendar Table I want the measure to calculate the SUM of an Amount column between 01/03/2021 and 14/03/2021, here's what I had so far but currently it is only doing the SUM of the date I selected:

VAR SelectedDate = SELECTEDVALUE ( 'Calendar'[Date] )

VAR CalcExpr = CALCULATE (

    SUM ( 'Sales'[Amount] ),

    'Country'[Country] = "United Kingdom",

    'Calendar'[Date] = SelectedDate,

    DATESBETWEEN ( 'Sales'[Sale Date], STARTOFMONTH ( VALUES ( 'Calendar'[Date] ) ), SelectedDate )

)

RETURN

    CalcExpr

I have tried disabling the "Edit Interaction" between the slicer and the visual but this does not result in what I am after, any help would be much appreciated

[besomebody20 · Accepted solution]

Hi @Adam01 ,

There is an option to use time intelligence DAX functions. The function DATESMTD should return the results you are expecting. 

The below two measures should return the month to date total sales amount.

Sales Amount = SUM('Sales'[Amount])

Sales Month to Date = 
CALCULATE(
    [Sales Amount],
    DATESMTD('Calendar'[Date])
)

 There are prerequisites to use time intelligence DAX functions. The Calendar table needs to be marked as a Date table, and the Date column must be complete sequence of date values with no missing days between the dates.

Marking a table as a Date table:
In the data modeling view, right click the date table. Then, hover over Mark as date table and select Mark as date table.

Finally, in the pop-up window select the date column in the table and click the OK button.

Then, create a relationship one-to-many between the date table and Sales table:

Next, in the report visuals, make sure the date column from the Calendar table is used in the slicer and the table visual. This should display the month to date sum for a given row in the table.

If the date column from the Sales table is used, it will not perform the calculation correctly

[Adam01]

Hello @PabloDeheza thank you for getting back to me

Unfortunately this DAX only returns the amount for that specific date (in this case that is the 14th of March 2021) instead of the amounts for dates leading up to 14/03/2021

VAR _SelectedDate = SELECTEDVALUE ('Calendar'[Date] )

VAR _StartOfMonth = EOMONTH (_SelectedDate, -1 ) + 1

VAR _CalcExpr =

    CALCULATE (

        SUM ('Sales'[Amount] ),

        'Country'[Country] = "United Kingdom",

        DATESBETWEEN ( 'Sales'[Sale Date], _StartOfMonth, _SelectedDate ) )

RETURN

    _CalcExpr

[PabloDeheza]

Hi @Adam01 !
It should be working, I tried with my data and it does.

If I select in a filter the 14th of march it returns the sum from the 1st to the 14th

[Adam01]

Hi @PabloDeheza are you able to share this pbix file with me so I can download and play about with it myself? its most likely I'm missing something obvious in the model / visual itself

[PabloDeheza]

Hi @Adam01 !
Try with the following:

VAR _SelectedDate =
	SELECTEDVALUE( 'Calendar'[Date] )
VAR _StartOfMonth =
	EOMONTH( _SelectedDate, -1 ) + 1
VAR _Result =
	CALCULATE(
		SUM( 'Sales'[Amount] ),
		'Country'[Country] = "United Kingdom",
		DATESBETWEEN ( _StartOfMonth, _SelectedDate )
	)
RETURN
	_Result

Let me know if that helps!