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
Anonymous
Not applicable

Measure for non month end

I need to create some Power BI measures for Total Charges and Previous Month Charges however my data for each billing period looks like this:

 

Dec Billing Period - Start 11/28 - 12/27

Jan Billing Period - Start 12/28 - 1/27

Feb Billing Period - Start: 1/28 - 2/27

 

Each billing file is being merged into one table with the following column Billing[UsageStartTIme]  

 

Need something dynamic that could be used in different measures etc.  Also, how would I use the 

Current Period Charges = 

Previous Period Charges = 

 

I've read about using time intelligence with filter and min/max but not sure where to start. 

3 REPLIES 3
Greg_Deckler
Super User
Super User

You will need to split out your start and stop times, then you could use something like: 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@Greg_Deckler 
I do have a beginning and end column in my data table, both examples are good, I'm right now digging into the measure you have in the periodic billing example to see if that would work, I'll let you know. 

Another potential solution I had found before was this one but couldn't quite get it to work for me.
https://community.powerbi.com/t5/Desktop/Using-non-month-end-relative-date/td-p/289048

 

Thanks for responding.

Anonymous
Not applicable

What I ended up doing was using the billing period column I had created (201912, 201901, 201902) for each billing file, then performed a Rankx on the list and ordered them based on the latest billing period being 1, previous 2 etc.  

Rank = RANKX('Billing Table',[BillingPeriod],,DESC,Dense)



Charges Current Period =
CALCULATE(SUM(Table[Charges]), 'Billing Table'[Rank]=1)

Charges Previous Period =
CALCULATE(SUM(Table[Charges]), 'Billing Table'[Rank]=2)

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.