cancel
Showing results for 
Search instead for 
Did you mean: 
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
Community Champion
Community Champion

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!
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
v-yuezhe-msft
Microsoft
Microsoft

@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
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!