Difference between two dates for individual business months

0
votes

Objective/ Problem: How can I determine the difference/ remaining days (workday) between today and the end of a individual business month within a dax measure?

Info / General: I have a calendar table in PowerPivot that shows me per day what business month it is and whether it is a workday day. This table has the following structure: Date - Business Month - Workday factor.

That is, each business month has a different start and end date, as opposed to the normal month.

Previous approach: I think I first have to find a variable that shows me the maximum date per month and then use calculate and filter to find the difference to today?

How would you implement that?

Best Regards

Edit: That is my actual approach: Start Date:

Calculate(MIN(Calendar[Business Month]))

End Date: 

Calculate(Max(Calendar[Business Month]))

That ones show me the start and end date for each Business Month. Now I need to figure out, how many workdays are between today and End Date.

The Calendar looks like this: enter image description here

Workday = 1
Day off = 0
2
excelpowerbidaxpowerpivot
Welcome to SO! When you place a question try to add a minimum content: input sample, expected output sample, what did you try, research and where are you stacked. Could you show us your input? – David García Bodego
Hi and welcome to stackoverflow. It seems like you're well underway sorting your own problem already. Can you show us your formula's or VBA code that has gotten you this far? I'm afraid we're not here to write your code for you, but we can definitely help improve your efforts. In your case I suggest starting here. – Plutian
Thanks for your reply! I have edited the post. – joshua

2 Answers

0
votes

You are nearly there, you need to combine the functions:

Total Working Days = 
VAR maxMonth = MAX(Calendar[Business Month])
VAR minMonth = MIN(Calendar[Business Month])
RETURN
CALCULATE(SUM('Calendar'[Workday Factor]), FILTER('Calendar', [Date] >= minMonth && [Date] <= maxMonth))

I also recommend that you create a calendar dimension that has your standard calendar and business calendar in it, and nay logic around it, like current day/week/month/year, quarters, or periods and use that to connect to your data table

Hope that helps

0
votes

If I understand correctly what you want is to calculate the number of day between today and the end of a selected business month.

So If you select with a slicer a particular Month you can use dax time intelligence.

Measure = Calculate(Sum(CalendarCustom[WorkDay Factor]),CalendarCustom[Date] > NOW())

Calculate function let you add filter to a table and evaluate this filtered table. Here the table will be filtered by your slicers for the Monthselected and you just have to add filter to be sure that your date is greater than today.