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
CassieK
Frequent Visitor

Financial Account Summary Data - Need a Date Field

I am working on building a financial dashboard in Power BI and I have an Account Summary table with period balances for all accounts, but the data does not include any dates. It has Period ID and Fiscal Year. I want to add some time intelligence measures to the report but I can't do that without a date. Is there any way to return the end of month date based off of the Peiod ID and the Fiscal year?

 

 

4 REPLIES 4
danextian
Super User
Super User

Hi @CassieK,

 

You can create a calculated column using DATE function in DAX. 

DATE( YEAR, MONTH, DAY) 

In your case, we can just use the fiscal year column as the year but we need to be able to at least get the month and just use a constant number for DAY. Your Period ID column might give us a clue what the months should be. 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
v-yuezhe-msft
Employee
Employee

@CassieK,

Is  there any possibility to create a date column based on the existing columns in your table? Could you please share dummy data of your table and post your desired result?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Here is a sample of the data with the desired resulting End of Period Date Column:

YearPeriod IDAccount NumberPeriod Balance End of Period Date
2017161000-2021000 31-Aug-16
2017261000-2021000 30-Sep-16
2017361000-2021000 31-Oct-16
2017461000-2021000 30-Nov-16
2017561000-2021000 31-Dec-16
2017661000-2021000 31-Jan-17
2017761000-2021000 28-Feb-17
2017861000-2021000 31-Mar-17
2017961000-2021000 30-Apr-17
20171061000-2021000 31-May-17
20171161000-2021000 30-Jun-17
20171261000-2021000 31-Jul-17
2018161000-2021000 31-Aug-17
2018261000-2021000 30-Sep-17
2018361000-2021000 31-Oct-17
2018461000-2021000 30-Nov-17
2018561000-2021000 31-Dec-17
2018661000-2021000 31-Jan-18
2018761000-2021000 28-Feb-18
2018861000-2021000 31-Mar-18

 

Our Fiscal Year runs Aug 1 - July 31, so F2017 period 1 = Aug 1, 2016-Aug 31, 2016, Period 2 = Sept 1, 2016-Sept 30, 2016...Period 6 = Jan 1, 2017-Jan 31, 2017...Period 12 = Jul 1, 2017 - Jul 31, 2017.

 

The returned day could be a constant 28th of every month if that would make it easier.

 

I have tried buidling a DATE DAX funciton as well as an ENDOFMONTH function, but I haven't been able to get one that works.

 

Thanks!

Anonymous
Not applicable

Not sure how was your data structure, but ENDOFMONTH function can return the last date of the month in the current context for the specified column of dates.

 

 

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.