cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Regular Visitor

## DAX how to write - Monthly total appear in the first week

Hello everyone,

Intro: Im new comer to dax and encounter a problem in dax. Really appreciate your help!

Problem description:

1. Monthly subtotal appear only in the first week

2. That subtotal "can be" accumulative

Simplified Example:

 Month Week Date Sales Month Total (What i want) Cumulative Month Total (What i want) Jan 1 2023-01-01 5 25 25 Jan 2 2023-01-08 5 0 25 Jan 3 2023-01-15 5 0 25 Jan 4 2023-01-22 5 0 25 Jan 5 2023-01-29 5 0 25 Feb 1 2023-02-05 5 20 45 Feb 2 2023-02-12 5 0 45 Feb 3 2023-02-19 5 0 45 Feb 4 2023-02-26 5 0 45

The codes I write:

1. Month Total=

IF(MIN([WEEK])=1,

CALCULATE([Sales], ALLEXCEPT([Month]),

0)

2. Cumulative Month Total=

CALCULATE([Month Total], FILTER(ALL(Calendar),Calendar[Date]<=MAX(Calendar[Date])))

Result:
For the 1st code, i get what I want. But for the 2nd code its not working.

Can anyone help! Thx!!!

Arthur

1 ACCEPTED SOLUTION
Community Support

Hi @Arthur94 ,

I suggest you to create a calendar table with [YearMonth] column.

``````Calendar =
CALENDARAUTO (),
"Year", YEAR ( [Date] ),
"MonthNum", MONTH ( [Date] ),
"Month", FORMAT ( [Date], "MMM" ),
"WeekStart",
[Date] - WEEKDAY ( [Date], 1 ) + 1,
"YearMonth",
YEAR ( [Date] ) * 100
+ MONTH ( [Date] )
)``````

Measure:

``````Cumulative Month Total =
CALCULATE (
[Month Total],
FILTER (
ALL ( 'Calendar' ),
'Calendar'[YearMonth] <= MAX ( 'Calendar'[YearMonth] )
)
)``````

Result is as below.

Best Regards,
Rico Zhou

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

3 REPLIES 3
Regular Visitor

Sorry for the table format. Realized the date and sales are to close after posted. Sales are 5 for each week.

Community Support

Hi @Arthur94 ,

I suggest you to create a calendar table with [YearMonth] column.

``````Calendar =
CALENDARAUTO (),
"Year", YEAR ( [Date] ),
"MonthNum", MONTH ( [Date] ),
"Month", FORMAT ( [Date], "MMM" ),
"WeekStart",
[Date] - WEEKDAY ( [Date], 1 ) + 1,
"YearMonth",
YEAR ( [Date] ) * 100
+ MONTH ( [Date] )
)``````

Measure:

``````Cumulative Month Total =
CALCULATE (
[Month Total],
FILTER (
ALL ( 'Calendar' ),
'Calendar'[YearMonth] <= MAX ( 'Calendar'[YearMonth] )
)
)``````

Result is as below.

Best Regards,
Rico Zhou

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

Regular Visitor

Hi Rico,

I used your code in my data model. That solved my problem.

Thank you so much. Very helpful!!!😁

Arthur

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors