Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Adam01
Advocate I
Advocate I

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

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
1 ACCEPTED SOLUTION
besomebody20
Resolver I
Resolver I

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.

besomebody20_0-1666963100089.png

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:

besomebody20_1-1666963328864.png

 

 

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.

besomebody20_2-1666963411445.png

 

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

besomebody20_3-1666963545096.png

 

 

View solution in original post

5 REPLIES 5
besomebody20
Resolver I
Resolver I

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.

besomebody20_0-1666963100089.png

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:

besomebody20_1-1666963328864.png

 

 

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.

besomebody20_2-1666963411445.png

 

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

besomebody20_3-1666963545096.png

 

 

Adam01
Advocate I
Advocate I

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

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

PabloDeheza_0-1666960422803.png

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

PabloDeheza_1-1666960478040.png

 

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
Solution Sage
Solution Sage

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!

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.