Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
eminkoff
Regular Visitor

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

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!

1 ACCEPTED SOLUTION
Phil_Seamark
Employee
Employee

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)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

7 REPLIES 7
Phil_Seamark
Employee
Employee

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)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

It worked!! Thank you!

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

@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 Smiley Happy

 

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...

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

 

Dates.png

 

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

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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 Smiley Happy

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.