CODEWITHSUNDEEP
powerbidax
SherlockSpreadsheets
SherlockSpreadsheets

Reputation: 2370

DAX calculated column - NEXTMONTH() value, shift row context

I have successfully calculated "WorkDaysPassed - Current Mo". This is a calculated column that counts the number of working days have passed in the month, only for the current month and the value is printed for each date record in the current month. Current month is ending '1/31/2022'.

I need help figuring out how to print this same value on the prior months date records. Prior month is ending '12/31/2021'. The calculated column would be named "WorkDaysPassed - Closing Month". I think the NEXTMONTH() calculation could do this in in aggregate function.

How to do this in a calculated column? Below is what I have tried to calculate so far.

Correct calculation

  1. This is a calculated column that counts the number of working days have passed in the month, only for the current month and the value is printed for each date record in the current month.
WorkDaysPassed - Current Mo = 
IF( NOT('Calendar'[IsCurrentMonth]), BLANK(), 
CALCULATE(
    COUNTROWS ( 'Calendar' ),
    'Calendar'[Date],
    'Calendar'[IsWorkingDay] = TRUE 
    && 'Calendar'[Date] <= TODAY ()
    && MONTH('Calendar'[Date]) = MONTH(TODAY())
    && YEAR('Calendar'[Date]) = YEAR(TODAY())
))

Attempted calculations

  1. It is printing 186 for every row in the date table.
WorkDaysPassed - Closing Month = 
CALCULATE(SUM('Calendar'[WorkDaysPassed - Current Mo]), ALL('Calendar') )

Table data sample

enter image description here

Upvotes: 0

Views: 795

Answers (1)

SherlockSpreadsheets
SherlockSpreadsheets

Reputation: 2370

I was able to get this working by defining a Calculated Measure and the referencing the measure in the Calculated Column.

Calculated Measures:

  1. WorkingDays - MTD Current Mo
WorkingDays - MTD Current Mo = 
IF( NOT(VALUES('Calendar'[IsCurrentMonth])), BLANK(),
CALCULATE (
        COUNTROWS ( 'Calendar' ),
        'Calendar'[Date],
        'Calendar'[IsWorkingDay] = TRUE 
        && 'Calendar'[Date] <= TODAY ()
        && MONTH('Calendar'[Date]) = MONTH(TODAY())
        && YEAR('Calendar'[Date]) = YEAR(TODAY())
))
  1. Working Days MTD - Closing Month
Working Days MTD - Closing Month = 
IF(NOT(HASONEVALUE('Calendar'[Mo-Yr])),BLANK(), 
CALCULATE (
    [WorkingDays - MTD Current Mo],
    NEXTMONTH ( 'Calendar'[Date] ) 
))

Calculated Columns:

enter image description here

Upvotes: 0

Related Questions