Forum Discussion
Custom Month for Transactions
Hello,
I am working on an accounting report and want to denote the month that the transaction occured but those months based on our financial calendar.
I have a table created that breaks down each month
| StartDate | EndDate | Month |
| 01/03/21 | 01/30/21 | January |
| 01/31/21 | 2/27/21 | February |
Then I have a transaction table
| Job | Accounting_Date | Total_Amount | Transaction_Type | Month |
| XXX-XXX | 01/15/21 12:00:00 AM | 380 | EQ Cost |
Just as a test I had a measure for the Month colum as
cfahner oops I made a mistake, try again:
Month Column = VAR __tranDate= Job[Accounting_Date] RETURN CALCULATE ( MAX ( NewCalendar[Month] ), __tranDate >= NewCalendar[StartDate], __tranDate <= NewCalendar[EndDate] )✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
3 Replies
- parry2k
Super User
cfahner try this by adding a new column
Month = VAR __tranDate= TransactionTable[AccountingDate] RETURN CALCULATE ( MAX ( DateTable[Month] ), DateMonth[StartDate] >= __tranDate, DateMonth[EndDate] <= __tranDate)✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
That did not give any errors but also did not provide any data within there but will take a look at it more later also.
- parry2k
cfahner oops I made a mistake, try again:
Month Column = VAR __tranDate= Job[Accounting_Date] RETURN CALCULATE ( MAX ( NewCalendar[Month] ), __tranDate >= NewCalendar[StartDate], __tranDate <= NewCalendar[EndDate] )✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
