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

[Arthur94]

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

[Arthur94]

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

[v-rzhou-msft · Accepted solution]

Hi @Arthur94 ,

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

Calendar = 
ADDCOLUMNS (
    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.

[Arthur94]

Hi Rico,

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

Thank you so much. Very helpful!!!😁

Arthur