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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mowen399
Frequent Visitor

create table with month/fiscal qtr/fiscal yr sales by customer group

Looking for some help here. I need to create a table that has Month Sales, Fiscal Qtr Sales, and Fiscal YR sales by customer groups. The table must be able to be filtered by slicers by selecting month and year. Based on the month/year the data should update sales figures up to the date selected. Issue i'm having is with history,

 

I have already added fiscal qtr and fiscal yr columns on my table. How can i create a measure or new column that will calculate the sum of sales based on customer group and qtr. 

 

Should look something like this:

 

Cust group    Monthly Sales    Qtr Sales   FY Sales

 Sample             100                     200           200

1 ACCEPTED SOLUTION

Hi @mowen399,

 

How does the data source look like? If they are in one table or are organized with proper relationships, we just need to sum the sales. The result will be recount by the context of the customer groups. There are three time intelligence functions that suit for your scenario. TOTALMTDTOTALQTDTOTALYTD

 

We could use this formula in your latest post.

Measure =
CALCULATE (
    TOTALQTD ( SUM ( Sales[Quantity] ), 'Date'[Date] ),
    DATESBETWEEN (
        'Date'[Date],
        STARTOFQUARTER ( 'Date'[Date] ),
        MAX ( 'Date'[Date] )
    )
)

If this couldn't solve your problem, please post a sample including relationships.

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-jiascu-msft
Employee
Employee

Hi @mowen399,

 

Could you please mark the proper answer if it's convenient for you? That will be a help to others.

 

Best Regards!
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

You need to create a "Dates" table and then join it to your sales table.  Then you will be able to break sales up by every possible time frame.

 

 

Instructions are as followed:

 

1 - Switch to Data View by clicking the middle icon on the left under the ribbon.

2 - Activate the “Modeling” ribbon on top of the page and click the New Table button. The expression “Table =” will appear in the Formula Bar.  Replace “Table=” with:

DateDimension = CALENDAR( "1/01/2015", "12/31/2018" )

Then, click on “New Column” for each line below.

FullYear = YEAR([Date])

ShortYear = VALUE(Right(Year([Date]),2))

MonthNumber = MONTH([Date])

MonthNumberFull = FORMAT([MonthNumber],"00")

MonthFull = FORMAT([Date], "MMMM")

MonthAbbr = FORMAT([Date], "MMM")

WeekNumber = WEEKNUM([Date])

WeekNumberFull = FORMAT(Weeknum([Date]), "00")

DayOfMonth = DAY([Date])

DayOfMonthFull = FORMAT(Day([Date]),"00")

DayOfWeek = WEEKDAY([Date])

DayOfWeekFull = FORMAT([Date],"dddd")

DayOfWeekAbbr = FORMAT([Date],"ddd")

ISODate = [FullYear] & [MonthNumberFull] & [DayOfMonthFull]

FullDate = [DayOfMonth] & " " & [MonthFull] & " " & [FullYear]

QuarterFull = "Quarter " & ROUNDDOWN(MONTH([Date])/4,0)+1

QuarterAbbr = "Qtr " &ROUNDDOWN(MONTH([Date])/4,0)+1

Quarter = "Q" &ROUNDDOWN(MONTH([Date])/4,0)+1

QuarterNumber = ROUNDDOWN(MONTH([Date])/4,0)+1

QuarterAndYear = DateDimension[Quarter]&DateDimension[FullYear]

MonthAndYearAbbr = DateDimension[MonthAbbr] & " " & [FullYear]

QuarterAndYearNumber = [FullYear] & [QuarterNumber]

YearAndWeek = VALUE([FullYear] &[WeekNumberFull])

YearAndMonthNumber = Value(DateDimension[FullYear] & DateDimension[MonthNumberFull])

 

 

When finished, highlight each of the six “Column” below and click on “Sort by Column” and set to the correct sort.

Column Sort                       By Column

MonthAbbr                        MonthNumber

DayOfWeekFull                DayOfWeek

DayOfWeekAbbr              DayOfWeek

Quarter And Year             QuarterAndYearNumber

FullDate                               Date

MonthAndYearAbbr       YearAndMonthNumber

MonthFull                           MonthNumber

I have a date table created. The issue i am having is calculating the quarter sales based off of month selected in slicer. For example, we run on a fiscal April-March.  If a user selects feb 2017 as there filter,the quarter should only return sales history for Jan/Feb. I can get the full quarter (Jan-March), but i only want Jan & Feb. 

Hi @mowen399,

 

How does the data source look like? If they are in one table or are organized with proper relationships, we just need to sum the sales. The result will be recount by the context of the customer groups. There are three time intelligence functions that suit for your scenario. TOTALMTDTOTALQTDTOTALYTD

 

We could use this formula in your latest post.

Measure =
CALCULATE (
    TOTALQTD ( SUM ( Sales[Quantity] ), 'Date'[Date] ),
    DATESBETWEEN (
        'Date'[Date],
        STARTOFQUARTER ( 'Date'[Date] ),
        MAX ( 'Date'[Date] )
    )
)

If this couldn't solve your problem, please post a sample including relationships.

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for the help. I was missing the Max as part of the formula. 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors