cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Keropi79 Frequent Visitor
Frequent Visitor

Extracted value with start & end date information

Hi,

I'm new to Power BI and I need help on the DAX function to simulate the following scenario.

My main table only have the following information:

CategoryStart DateEnd dateRevenue
Equipment1/04/201830/04/201810000
Labor1/04/201830/03/202150000
Service1/04/201830/03/202140000

The output that I'm looking for is, for example, I want the value for particular periods: 1/4/18

Period:1/04/201830/09/2019
CategoryRevenueRevenue
Equipment10,000.00-  
Labor1,388.891,388.89
Service1,111.111,111.11

I tried to use the syntax DATESBETWEEN but it doesn't allow me to select start & end date as a column.

Do I need to make more than 1 DAX syntax to get the out that I want? If yes, what are the steps and syntax logic that I need to follow?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Extracted value with start & end date information

Hi @Keropi79 ,

 

Please check the following steps as below.

1. Create a calculated table as below.

Table 2 = CROSSJOIN(DISTINCT('Table'[Category]),'date')

2. To get the excepted result by the measure.

Measure 2 = 
VAR d =
    MAX ( 'Table 2'[date] )
VAR cate =
    MAX ( 'Table 2'[Category] )
VAR st =
    CALCULATE (
        MAX ( 'Table'[Start Date] ),
        FILTER ( 'Table', 'Table'[Category] = cate )
    )
VAR ed =
    CALCULATE (
        MAX ( 'Table'[End date] ),
        FILTER ( 'Table', 'Table'[Category] = cate )
    )
VAR diffmonth =
    DATEDIFF ( st, ed, MONTH )
RETURN
    IF (
        d > ed,
        BLANK (),
        IF (
            d >= st
                && d <= ed,
            DIVIDE (
                CALCULATE (
                    SUM ( 'Table'[Revenue] ),
                    FILTER ( 'Table', 'Table'[Category] = cate )
                ),
                diffmonth + 1
            )
        )
    )

Capture.PNG

 

BTW, pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: Extracted value with start & end date information

Hi @Keropi79 ,

 

Please check the following steps as below.

1. Create a calculated table as below.

Table 2 = CROSSJOIN(DISTINCT('Table'[Category]),'date')

2. To get the excepted result by the measure.

Measure 2 = 
VAR d =
    MAX ( 'Table 2'[date] )
VAR cate =
    MAX ( 'Table 2'[Category] )
VAR st =
    CALCULATE (
        MAX ( 'Table'[Start Date] ),
        FILTER ( 'Table', 'Table'[Category] = cate )
    )
VAR ed =
    CALCULATE (
        MAX ( 'Table'[End date] ),
        FILTER ( 'Table', 'Table'[Category] = cate )
    )
VAR diffmonth =
    DATEDIFF ( st, ed, MONTH )
RETURN
    IF (
        d > ed,
        BLANK (),
        IF (
            d >= st
                && d <= ed,
            DIVIDE (
                CALCULATE (
                    SUM ( 'Table'[Revenue] ),
                    FILTER ( 'Table', 'Table'[Category] = cate )
                ),
                diffmonth + 1
            )
        )
    )

Capture.PNG

 

BTW, pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

Keropi79 Frequent Visitor
Frequent Visitor

Re: Extracted value with start & end date information

Hi @v-frfei-msft 

Thanks for the reply. I can't open the pbix file. I still use the older version of PB.

Regarding the solution. The new table is created by crossjoining the Category and date. My question is which date is being used, calendar date (I have a separate table for the calendar date itself), so should I join this table with Category or should I join the start date and the end date from the same table itself?

 

Thanks.

Super User
Super User

Re: Extracted value with start & end date information

Hi,

If the start date is April 1, 2018 and end date is June 13, 2021, then how will you calculate the tota number of months?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (4,036)