cancel
Showing results 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

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
Resolver I

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

5 REPLIES 5
Resolver I

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

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

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

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

Solution Sage

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!

Announcements

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 Monthly Update - June 2024

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

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors