Same working day last month

[Silvafau]

I need to compare the sales of this month vs same working day of last month. For example, the second workday of August22 was 02/Aug/22. The second workday of previous month was 04/Jul/22.  The measure should be able to compare sales figures between these datas. The usual DAX formulas I know for last month returns same day last month (in this case 02/Jul/22) but I need same workday. Can someone help me?

[Anonymous]

Hi @Silvafau ,

My steps are as follows.

1. New a Calendar Table and create relationship with 'Table'[Date].

WorkdaySort = 
VAR _sort =
    CALCULATE (
        COUNT ( 'Calendar'[Date] ),
        FILTER (
            'Calendar',
            'Calendar'[Month] = EARLIER ( 'Calendar'[Month] )
                && 'Calendar'[Date] <= EARLIER ( 'Calendar'[Date] )
                && 'Calendar'[Year] = EARLIER( 'Calendar'[Year] )
                && NOT 'Calendar'[Workday] IN { 6, 7 }
        )
    )
VAR _result =
    IF ( NOT 'Calendar'[Workday] IN { 6, 7 }, _sort )
RETURN
    _result

2. New 2 measures.

sales of this month = 
SUM('Table'[Sales])

sales of last month = 
VAR _date =
    SELECTEDVALUE ( 'Calendar'[Date] )
VAR _month =
    MONTH ( _date )
VAR _year =
    YEAR ( _date )
VAR _workday_sort =
    CALCULATE ( MAX ( 'Calendar'[WorkdaySort] ), 'Calendar'[Date] = _date )
VAR _sales1 =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALL('Calendar'),
            'Calendar'[Year] = _year
                && 'Calendar'[MonthSort] = _month - 1
                && 'Calendar'[WorkdaySort] = _workday_sort
        )
    )
VAR _sales2 =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALL('Calendar'),
            'Calendar'[Year] = _year - 1
                && 'Calendar'[MonthSort] = 12
                && 'Calendar'[WorkdaySort] = _workday_sort
        )
    )
VAR _sales =
    IF ( _month <> 1, _sales1, _sales2 )
RETURN
    _sales

3. Data point table.

As shown in the chart, sometimes there are fewer working days in the previous month than in the current month, for example, there is no 22nd or 23rd working day in July, which will be shown as empty in the chart.

The PBIX file is attached for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[Silvafau]

Thanks a lot for your contribution. I could achieve great progress with it.

However, when I put the new measure "sales of last month" in my report, I get some blank data...

In the calendar, I defined the WorkdaySort for Saturday and Sunday as the same work day as Friday, instead of blank.

    WorkDaySort =

        VAR WD = COUNTROWS(FILTER(tCalendario;WEEKDAY([date]) in {2;3;4;5;6} && [date] <= [Data] && YEAR([date]) = YEAR([Data])&& MONTH([date]) = MONTH([Data]) ))

        RETURN

        IF(ISBLANK(WD);1;WD);

This is because, in the case of sales on non-working days, I need to consider it as sales for the previous work day.

In the first table below, we see the result of the measure "sales of last month" as you proposed, in my report, with the date column displayed. When I take out the column Date (second table), the sales for work days 5;10;15 are not displayed/blank. In the third table, you can see the correct sales for the previous month as a reference.

Do you have any idea what is wrong? 

Additionally, my intention is to use this measure to compare accumulated sales MTD. Do you have any idea also for this next step?

[daXtreme]

Sure. You need to have a calendar in which you'll have a column that will store the workday number of the day in the month. So, let's say that Sat and Sun are not workdays to keep it simple and there are no other such days. In each month the days would be numbered 1, 2, 3,... and the ones that are non-workdays would get the number of -1 (not 0). Such a column would be hidden as it's only needed for calculations. Once you've got such a column, the measure would be something along these lines:

[Measure (SWDPM)] = // SWDPM - Same Workday Previous Month
var CurrentWorkdayNumber =
  SELECTEDVALUE( 'Calendar'[Workday Number], -1 )
// MonthID must be an integer that is unique
// across all months of all years (not only
// within one year). It starts with 1 and goes
// up by one till the very last month in your calendar. 
// Must be consecutive. This column
// will be hidden as it's only needed for calculations.
var CurrentMonthID =
  SELECTEDVALUE( 'Calendar'[MonthID] )
// First, make sure only one day is visible
// and it's a workday.
var IsOneWorkdayVisible = 
  CurrentWorkdayNumber <> -1
var Result = 
  IF( IsOneWorkdayVisible,
    CALCULATE(
      [Measure],
      'Calendar'[Workday Number] = CurrentWorkdayNumber,
      'Calendar'[MonthID] = CurrentMonthID - 1,
      REMOVEFILTERS( 'Calendar' )
    )
  )
return
  Result
  
// Bear in mind that this measure may return BLANK
// for a valid workday in a month. This is because
// months, on average, will not have the same number
// of workdays. So, the 22nd workday in some month
// may not have a counterpart in the prior month. In
// such a case the measure will return BLANK. It'll
// also return BLANK if you've selected a non-workday.
// It'll also return BLANK if you've selected a day
// whose counterpart in the prior month has [Measure]
// that returns BLANK. If you want to be able to distinguish
// between all these cases, then you have to code
// additional logic.