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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.