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!