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

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

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.