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
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_IDCustomer_IDMonthBudget
001AAAJan 2021€ 1,000
001AAAFeb 2021€ 2,500
002BBBJan 2021€ 3,000
002BBBFeb 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
v-xiaotang
Community Support
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:

v-xiaotang_1-1621563946299.png

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
Super User
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.

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors