Calculate & Comparison Current Week of Month with Last Month of Week

[ADSL]

Hi BI Community Team,

I have a sales order table that contain data 02 months - Nov'23 & Dec'23. With this data, I am standing in Dec'23 by week then want to see and compare with last month by week as the screenshot below. 

Example:  W1-Dec vs W1-Nov | W2-Dec vs W2-Nov

Any suggestion/advise? 

[mh2587]

WoW Sales Comparison = //Try this one might help you
VAR CurrentFirstWeekSale =
    CALCULATE ( [Total Sales], 'Calendar'[WeekNumber of Month] = 1 )
VAR PreviouseFirstWeekSale =
    CALCULATE (
        [Total Sales],
        DATEADD ( 'Calendar'[Date], -1, MONTH ),
        'Calendar'[WeekNumber of Month] = 1
    )
RETURN
    CurrentFirstWeekSale - PreviouseFirstWeekSale

[isjoycewang]

Hi @ADSL,

Please see attached demo file here.

You can create another measure and add it into the clustered column chart as below.

Total Sales Same Week Last Month = 
VAR _week = SELECTEDVALUE('Calendar'[Week of Month])
VAR _month = SELECTEDVALUE('Calendar'[Date].[MonthNo])
RETURN 
CALCULATE( SUM('Sales Order'[Amt No VAT]), FILTER(ALL('Calendar'), 'Calendar'[Week of Month] = _week && 'Calendar'[Date].[MonthNo] = _month-1))

If you only want to see Nov data when you have Dec data:

Total Sales Same Week Last Month = 
VAR _week = SELECTEDVALUE('Calendar'[Week of Month])
VAR _month = SELECTEDVALUE('Calendar'[Date].[MonthNo])
RETURN 
IF( CALCULATE( SUM('Sales Order'[Amt No VAT]), 'Calendar'[Week of Month] = _week) >0,
CALCULATE( SUM('Sales Order'[Amt No VAT]), FILTER(ALL('Calendar'), 'Calendar'[Week of Month] = _week && 'Calendar'[Date].[MonthNo] = _month-1)),
BLANK())

Best Regards,

Joyce

[ADSL]

Hi @isjoycewang,

Thank you for your helpful feedback. 

If we want to change the slicer of month to "MonthInCalendar" as the screenshot below.

Any suggestion to revise the measure?

Best Regards,

[isjoycewang · Accepted solution]

Considering you'll need to calculate the last month, I think we should use month number in the measure.

I've also modified the measure a little bit as below.

Total Sales Same Week Last Month2 = 
VAR _SameWeek = SELECTEDVALUE('Calendar'[Week of Month])
VAR _LastMonth = IF(SELECTEDVALUE('Calendar'[MonthOfYear])= 1, 12, SELECTEDVALUE('Calendar'[MonthOfYear]) -1)
VAR _LastMonthYear = IF(SELECTEDVALUE('Calendar'[MonthOfYear])= 1, SELECTEDVALUE('Calendar'[Year])-1, SELECTEDVALUE('Calendar'[Year]))
RETURN 
IF( CALCULATE( SUM('Sales Order'[Amt No VAT]), 'Calendar'[Week of Month] = _SameWeek) >0,
CALCULATE( SUM('Sales Order'[Amt No VAT]), FILTER(ALL('Calendar'), 'Calendar'[Week of Month] = _SameWeek && 'Calendar'[MonthOfYear] = _LastMonth && 'Calendar'[Year] = _LastMonthYear)),
BLANK())

Best Regards,

Joyce

[ADSL]

Hi @isjoycewang,

Thank you for your valuable feebback and helup. 

All your measure are working, just want to change the slicer of month to choose month & year.

Best Regards,

[isjoycewang]

Glad to help!

The measure should work whether you use [MonthNo] or [MonthInCalendar] in the slicer.

Since the first month in a year might cause BLANK in the chart, provided above updated measure for your reference.