Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
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.
Proud to be a Super User!
@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
Here is a sample of the data with the desired resulting End of Period Date Column:
Year | Period ID | Account Number | Period Balance | End of Period Date | |
2017 | 1 | 61000-202 | 1000 | 31-Aug-16 | |
2017 | 2 | 61000-202 | 1000 | 30-Sep-16 | |
2017 | 3 | 61000-202 | 1000 | 31-Oct-16 | |
2017 | 4 | 61000-202 | 1000 | 30-Nov-16 | |
2017 | 5 | 61000-202 | 1000 | 31-Dec-16 | |
2017 | 6 | 61000-202 | 1000 | 31-Jan-17 | |
2017 | 7 | 61000-202 | 1000 | 28-Feb-17 | |
2017 | 8 | 61000-202 | 1000 | 31-Mar-17 | |
2017 | 9 | 61000-202 | 1000 | 30-Apr-17 | |
2017 | 10 | 61000-202 | 1000 | 31-May-17 | |
2017 | 11 | 61000-202 | 1000 | 30-Jun-17 | |
2017 | 12 | 61000-202 | 1000 | 31-Jul-17 | |
2018 | 1 | 61000-202 | 1000 | 31-Aug-17 | |
2018 | 2 | 61000-202 | 1000 | 30-Sep-17 | |
2018 | 3 | 61000-202 | 1000 | 31-Oct-17 | |
2018 | 4 | 61000-202 | 1000 | 30-Nov-17 | |
2018 | 5 | 61000-202 | 1000 | 31-Dec-17 | |
2018 | 6 | 61000-202 | 1000 | 31-Jan-18 | |
2018 | 7 | 61000-202 | 1000 | 28-Feb-18 | |
2018 | 8 | 61000-202 | 1000 | 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!
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.