Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DAX for Previous calculations

Hi All,   I have a report shown below a week ending date and a starts measure. For this report I need  1)How many starts last week compared to the previous week, previous month, previous year 2)H...
Need Help
v-kkf-msft's avatar
v-kkf-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi Anonymous ,

Based on your description, I create the following sample data. I first create a calculated column:

Worknum = WEEKNUM( 'Table'[WEEK ENDING], 2 )

 Then create the following measures:

Last Week = 
CALCULATE(
  SUM('Table'[STARTS]),
  LASTDATE( 'Table'[WEEK ENDING] )
)

 The cumulative starts for the same period of the previous year:

Same Period = 
var _worknum = 
  CALCULATE(
    MAX('Table'[Worknum]),
    DATESYTD('Table'[WEEK ENDING])
  )
var result =
  CALCULATE(
    SUM('Table'[STARTS]),
    FILTER(
      ALL('Table'), 
      WEEKNUM('Table'[WEEK ENDING],2) = _worknum 
      && 'Table'[WEEK ENDING] <> MAX('Table'[WEEK ENDING])
    )
  )
return result
Last Month = 
CALCULATE(
  SUM('Table'[STARTS]),
  DATESMTD('Table'[WEEK ENDING])
)
Last Year = 
CALCULATE(
  SUM('Table'[STARTS]),
  DATESYTD('Table'[WEEK ENDING])
)
Previous Week = 
CALCULATE(
  SUM('Table'[STARTS]),
  DATEADD(
    LASTDATE( 'Table'[WEEK ENDING] ),
    -7,
    DAY
  )
)
Previous Month = 
CALCULATE(
  SUM('Table'[STARTS]),
  PREVIOUSMONTH(
    LASTDATE('Table'[WEEK ENDING])
  )
)
Previous Year = 
CALCULATE(
  SUM('Table'[STARTS]),
  PREVIOUSYEAR('Table'[WEEK ENDING])
)

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Winniz,

Thank you for the solution I tried to implement the same in my report but I am getting Previous month and Previous Week as null. Also the the Starts is same as Last Week and Last month. Can you please tell me where am I going wrong