calculate the end date

[mthiru]

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

[v-yangliu-msft · Accepted solution]

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

[mthiru · Accepted solution]

@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

[v-yangliu-msft · Accepted solution]

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