Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
mthiru
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. 

 

StartDateEnddateDayvalueMonthvalueYearvalueEntityCodeEntityNameAmountDescriptionDocumentNumberAccountNameGLAccountMainAccountIdBUPVPARTNERPartnerNameLedger
2021-12-31 31122021A100 -888632   3411034110100 10  
2021-12-31 31122021A101 75    34110101 20  
2021-12-31 31122021A102 608.4    34110102 30  
2021-12-31 31122021A103 100    34110103 40  
2 ACCEPTED SOLUTIONS
v-yangliu-msft
Community Support
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:

vyangliumsft_0-1710296987405.png

 

 

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

View solution in original post

mthiru
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.

 

StartDateMainAccountIdBUPVtransactionEndDate
2018-07-03341101001nullnull
2018-07-03341101001nullnull
2018-07-03341101001nullnull
2018-07-03341101002nullnull
2018-07-04341101002nullnull
2018-07-05341101002nullnull
2018-07-0334110100352018-07-06
2018-07-0934110100362018-07-12
2018-07-1334110100342018-07-31
2018-07-0334110100412018-07-06
2018-07-0934110100422018-07-31

 

 

View solution in original post

2 REPLIES 2
mthiru
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.

 

StartDateMainAccountIdBUPVtransactionEndDate
2018-07-03341101001nullnull
2018-07-03341101001nullnull
2018-07-03341101001nullnull
2018-07-03341101002nullnull
2018-07-04341101002nullnull
2018-07-05341101002nullnull
2018-07-0334110100352018-07-06
2018-07-0934110100362018-07-12
2018-07-1334110100342018-07-31
2018-07-0334110100412018-07-06
2018-07-0934110100422018-07-31

 

 

v-yangliu-msft
Community Support
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:

vyangliumsft_0-1710296987405.png

 

 

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!