Forum Discussion

CassieK's avatar
CassieK
Icon for Advocate I rankAdvocate I
8 years ago

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 Fis...
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

CassieK's avatar
CassieK
Icon for Advocate I rankAdvocate I
8 years ago

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!