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 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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.

     

     

  • 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!

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    8 years ago

    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.