cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

Highlighted
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 Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)