Forum Discussion
calculate the end date
Here is a sample date from an SQL view. In Power BI, I need add to determine an End date for each row. The End date column should be End of the month date if there are no transactions for the month period grouped by (MainAccountID, BU and PV). If a transaction is posted on 7-3-2018 then the end date is equal to 7-31-2018 for each Group (MainAccountID, BU and PV). If there is nother transaction date for the group in the month, for example 7/31/2018, then the End date should be next index+1 start date 7/31/2018 - 1 = 7/30/2018.
| StartDate | Enddate | Dayvalue | Monthvalue | Yearvalue | EntityCode | EntityName | Amount | Description | DocumentNumber | AccountName | GLAccount | MainAccountId | BU | PV | PARTNER | PartnerName | Ledger |
| 2021-12-31 | 31 | 12 | 2021 | A100 | -888632 | 34110 | 34110 | 100 | 10 | ||||||||
| 2021-12-31 | 31 | 12 | 2021 | A101 | 75 | 34110 | 101 | 20 | |||||||||
| 2021-12-31 | 31 | 12 | 2021 | A102 | 608.4 | 34110 | 102 | 30 | |||||||||
| 2021-12-31 | 31 | 12 | 2021 | A103 | 100 | 34110 | 103 | 40 |
Hi mthiru ,
Here are the steps you can follow：
1. Create calculated column.
EndDate = var _count= COUNTX( FILTER(ALL('Table'), YEAR('Table'[StartDate])=YEAR(EARLIER('Table'[StartDate]))&&MONTH('Table'[StartDate])=MONTH(EARLIER('Table'[StartDate]))&& 'Table'[MainAccountId]=EARLIER('Table'[MainAccountId])&&'Table'[BU]=EARLIER('Table'[BU])&&'Table'[PV]=EARLIER('Table'[PV])&& 'Table'[transactions]<>BLANK()), [transactions]) var _discount= CALCULATE(DISTINCTCOUNT('Table'[StartDate]),FILTER(ALL('Table'), YEAR('Table'[StartDate])=YEAR(EARLIER('Table'[StartDate]))&&MONTH('Table'[StartDate])=MONTH(EARLIER('Table'[StartDate]))&& 'Table'[MainAccountId]=EARLIER('Table'[MainAccountId])&&'Table'[BU]=EARLIER('Table'[BU])&&'Table'[PV]=EARLIER('Table'[PV]) )) return SWITCH( TRUE(), _count=BLANK()&&_discount<>1,EOMONTH('Table'[StartDate],0), _count=BLANK()&&_discount=1,EOMONTH('Table'[StartDate],0)-1, MAXX( FILTER(ALL('Table'), YEAR('Table'[StartDate])=YEAR(EARLIER('Table'[StartDate]))&&MONTH('Table'[StartDate])=MONTH(EARLIER('Table'[StartDate]))&& 'Table'[MainAccountId]=EARLIER('Table'[MainAccountId])&&'Table'[BU]=EARLIER('Table'[BU])&&'Table'[PV]=EARLIER('Table'[PV])&& 'Table'[transactions]<>BLANK()),[StartDate]) )2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Anonymous , Thanks for the assist on this, really appreciate it. I was a little confused for a while I was not sure If I should try to calculate the End date in SQL query or separate table as a list, and getting overwhelmed by all the possible ways to do it. Here is a better revised table and better explanation:
If there are no transactions then it's null, and would not show up in Power BI. The transactions will occur on a business date so the End date will be (Next row) Startdate minus 1 if there is a distinct transaction for (MainAccountId, BU and PV) group occuring in the same month.
StartDate MainAccountId BU PV transaction EndDate 2018-07-03 34110 100 1 null null 2018-07-03 34110 100 1 null null 2018-07-03 34110 100 1 null null 2018-07-03 34110 100 2 null null 2018-07-04 34110 100 2 null null 2018-07-05 34110 100 2 null null 2018-07-03 34110 100 3 5 2018-07-06 2018-07-09 34110 100 3 6 2018-07-12 2018-07-13 34110 100 3 4 2018-07-31 2018-07-03 34110 100 4 1 2018-07-06 2018-07-09 34110 100 4 2 2018-07-31
2 Replies
Hi mthiru ,
Here are the steps you can follow：
1. Create calculated column.
EndDate = var _count= COUNTX( FILTER(ALL('Table'), YEAR('Table'[StartDate])=YEAR(EARLIER('Table'[StartDate]))&&MONTH('Table'[StartDate])=MONTH(EARLIER('Table'[StartDate]))&& 'Table'[MainAccountId]=EARLIER('Table'[MainAccountId])&&'Table'[BU]=EARLIER('Table'[BU])&&'Table'[PV]=EARLIER('Table'[PV])&& 'Table'[transactions]<>BLANK()), [transactions]) var _discount= CALCULATE(DISTINCTCOUNT('Table'[StartDate]),FILTER(ALL('Table'), YEAR('Table'[StartDate])=YEAR(EARLIER('Table'[StartDate]))&&MONTH('Table'[StartDate])=MONTH(EARLIER('Table'[StartDate]))&& 'Table'[MainAccountId]=EARLIER('Table'[MainAccountId])&&'Table'[BU]=EARLIER('Table'[BU])&&'Table'[PV]=EARLIER('Table'[PV]) )) return SWITCH( TRUE(), _count=BLANK()&&_discount<>1,EOMONTH('Table'[StartDate],0), _count=BLANK()&&_discount=1,EOMONTH('Table'[StartDate],0)-1, MAXX( FILTER(ALL('Table'), YEAR('Table'[StartDate])=YEAR(EARLIER('Table'[StartDate]))&&MONTH('Table'[StartDate])=MONTH(EARLIER('Table'[StartDate]))&& 'Table'[MainAccountId]=EARLIER('Table'[MainAccountId])&&'Table'[BU]=EARLIER('Table'[BU])&&'Table'[PV]=EARLIER('Table'[PV])&& 'Table'[transactions]<>BLANK()),[StartDate]) )2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Anonymous , Thanks for the assist on this, really appreciate it. I was a little confused for a while I was not sure If I should try to calculate the End date in SQL query or separate table as a list, and getting overwhelmed by all the possible ways to do it. Here is a better revised table and better explanation:
If there are no transactions then it's null, and would not show up in Power BI. The transactions will occur on a business date so the End date will be (Next row) Startdate minus 1 if there is a distinct transaction for (MainAccountId, BU and PV) group occuring in the same month.
StartDate MainAccountId BU PV transaction EndDate 2018-07-03 34110 100 1 null null 2018-07-03 34110 100 1 null null 2018-07-03 34110 100 1 null null 2018-07-03 34110 100 2 null null 2018-07-04 34110 100 2 null null 2018-07-05 34110 100 2 null null 2018-07-03 34110 100 3 5 2018-07-06 2018-07-09 34110 100 3 6 2018-07-12 2018-07-13 34110 100 3 4 2018-07-31 2018-07-03 34110 100 4 1 2018-07-06 2018-07-09 34110 100 4 2 2018-07-31