Division calculation between two dates

DAX DAX Calculations
1

My project is to capture the comp ratio between expense and budget. Easy enough, but I should have a comp ratio for the previous week and another for month to date thru previous week.

Now that I have the measures for the start and end dates, I need to do a division calculation
using those dates.
Below does not work

Comp Ratio Previous Week = 
CALCULATE(DIVIDE([Total Labor Expense],[Total Labor Budget])), 
DATESBETWEEN('Filters', [Previous Week Start Date], [Previous Week End Date])

I figured it out. How do I close this topic?

Comp Ratio MTD =
CALCULATE(
DIVIDE([Total Labor Expense],[Total Labor Budget]), DATESBETWEEN(budget_expense[weekendingDate], [Current MTD Start Date], [Current MTD End Date]))

I updated the DEMO .pbix

DEMO 2 - Budget and Expense by Pay Schedule.pbix (49.2 KB)

DEMO - Budget and Expense by Pay Schedule.pbix (50.5 KB)
budget_expense.xlsx (26.3 KB)
payroll_schedule.xlsx (10.6 KB)

2

Have you checked here:

Use the DatesMTD Function

3

I appreciate that, @lbarron20. However, the MTD is based on a payroll schedule so it is not the actual MTD as a calendar would be.

I have the pay schedule table included as an attachment. It identifies the start and end dates for the previous week, current month and several other identifiers. I need help with the measure that brings back the EOM WeekEndingDate, where CurrentMonth = 1 and end date goes back to the end of the previous week.

4

Bumping this post for more visibility.

5

Hi @LASHBURN - IF figured out, then can close the topic by Marking any response/own post as Solution. If still looking for answer, then let us know.

Thanks
Ankit J

6

Hi @LASHBURN ,

Please receive hereby a possible solution, based upon a standard DateAdd-function, and adding a standard continuous date table.
Be aware that time-measures with functions like DatesBetween and DateAdd requires a continuous Date table to work correctly.

PBIX attached:
DEMO - Budget and Expense by Pay Schedule v2.pbix (69.6 KB)

Workout explained:

1. Adjust data model, add standard Date Table including WeekEnd-Date, no need for “payroll schedule”

image

2. Set up measures with DateAdd

DS Comp ratio = DIVIDE( [DS Expense], [DS Budget],0)

DS Last week Comp ratio = CALCULATE( [DS Comp ratio], DATEADD(DateT[Date], -7, DAY))

MTD Comp ratio = DIVIDE([MTD Expenses], [MTD Budget], 0)

Last week MTD Comp ratio = CALCULATE( [MTD Comp ratio], DATEADD(DateT[Date], -7, DAY))

Note 1: the fixed formula DateAdd Date, -7 , Day works correct as in the fact-table “budget-expense” the previous week is always exactly 7 days back ! (at least in the test data)

Note 2: “comp ratio for month to date thru previous week.” is freely interpreted as last week MTD (??)

3 A possible report looks as follows:

image
image1239×497 55 KB

Note 3: be aware to select the WeekEnding Date from the Date-table, not from the fact-table.

I hope this meets your information requirements, however I am not sure what your exact definition is of “comp ratio for month to date thru previous week.”

Kind regards, Jan van der Wind

7

Hello @LASHBURN, it’s been a while since we got a response from you.

Just following up if you still need help with your inquiry?

If you do, kindly provide the information the experts requested above so they can help you further.

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

8

Hi @LASHBURN, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, you can create a new thread.