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

Anonymous
Not applicable

## sum of values for an entire quarter

Good morning everyone!

I have a matrix where
- the layers on the lines are sales agent and customers;
- columns are represented by quarters;
- as values I have sales and budgets.

In the budget table of the model I have something like that.

 SalesAgent_ID Customer_ID Month Budget 001 AAA Jan 2021 € 1,000 001 AAA Feb 2021 € 2,500 002 BBB Jan 2021 € 3,000 002 BBB Feb 2021 € 2,500

I would like a measure that calculates for each sales agent the total budget for the entire quarter, and not the value to date.
In the "Total" row of the matrix I need to view the total budget in the quarter, not to date.

For example, today we are in May, which is part of the second quarter. I want to see the full budget values until June.

How could I do that? Thank you in advance to everyone!

4 REPLIES 4
Community Support

Hi @Anonymous

-

Try this,

Create the measure:

``````TotalQuarter =
VAR _qua =
QUARTER ( SELECTEDVALUE ( 'Table'[Month] ) )
VAR _year =
YEAR ( SELECTEDVALUE ( 'Table'[Month] ) )
RETURN
CALCULATE (
SUM ( 'Table'[Budget] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[SalesAgent_ID] ),
_qua = [Measure_quarter]
&& _year = [Measure_year]
)
)``````

Result:

See sample file attached below.

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Super User

@Anonymous , better to create a date column like this

Date = "01 " & [Month] // Change datatype to date

Then you can have measure like this with time intelligence

Qtr Sales = CALCULATE(SUM(Table[Budget]),DATESQTD(ENDOFQUARTER('Date'[Date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Helper II

I am searching same type of things .

Anonymous
Not applicable

@amitchandak I don't understand how to create the date column Date = "01 " & [Month].

Can you please better explain? I have a table Canlendar in the model, with a link with the budget table by month.