cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## 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
2 ACCEPTED SOLUTIONS
Community Support

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

Frequent Visitor

@v-yangliu-msft , 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 2
Frequent Visitor

@v-yangliu-msft , 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

Community Support

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