sum of values for an entire quarter

[Anonymous]

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!

[v-xiaotang]

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.

[amitchandak]

@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.

[SachinNamdeo-20]

I am searching same type of things .

[Anonymous]

@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.