Custom invoice period (25th - 25th) - can't make up the formula

[eminkoff]

Hi there.

I have a customer with custom invoice period - 25th of prev month till 25th on current month.

What i need is to see hours spent in this period monthly, but pBI data hierarchy works with standard month period only.

So far i tried to make custom column in my data table with smth like this:

Column = IF('Date'[DayOfMonthNumber]<25;PREVIOUSMONTH('Date'[MM/DD/YYYY]);NEXTMONTH('Date'[MM/DD/YYYY]))

But it doesnt work for me 😞

Thanks in advance!

[Phil_Seamark · Accepted solution]

Try adding this column to your DATE table and see how you get on

Custom Month Grouping = 
	Var CutOff = 25
	Return  
		DATEADD('Dates'[Date] , 
		IF (DAY('Dates'[Date])  < CutOff  , 
			CutOff - DAY('Dates'[Date]) , 
			CutOff + DAY(EOMONTH('Dates'[Date],0)) - DAY('Dates'[Date])
			) ,DAY)

[eminkoff]

It worked!! Thank you!

Would be nice to have daily hierarchy level, but so far it works too!

[Sean]

@Phil_Seamark

I don't know if there's anyhting we can do about this because of the DATEADD function

but if your calendar extends only to today - 2/16/17 thru 2/23/17 return blanks?

EDIT: I tested it 15 thru 15 btw forgot to mention

[Phil_Seamark]

Oh does it?  I didn't scroll down to the end and most of my Date tables are based on the CALENDARAUTO function so generally have dates in the future.

What are the DAX date functions that don't rely on a Date Table?  Oh and did you see Marco Russos post on marking tables as a Date table in Power BI in a round about way?

http://sqlblog.com/blogs/marco_russo/archive/2017/02/22/mark-as-date-table-in-power-bi-dax-powerbi.a...

[Sean]

I'm using a Calendar but its PowerQuery generated to today only...

Hey I wonder if we can create bins instead of the date 2/15/17 to actually say "1/15/17 - 2/15/17" ?

[Phil_Seamark]

I guess once you have the date calculating correctly you can build strings like you suggest.  Just need to make sure they sort by something useful

I got my "M is for Data Monkeys" book in the post yesterday from Amazon so will hopefully be able to post some useful PQ tips.

[Sean]

I've been waiting for the successor to the Definitive Guide to DAX but they keep pushing the release date currenty I think May

http://www.sqlbi.com/books/analyzing-data-with-microsoft-power-bi-and-power-pivot-for-excel/

You may be able to pick up a copy in June

Looks like you'll be a shoo-in for MVP