Extracted value with start & end date information

[Keropi79]

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:

Category	Start Date	End date	Revenue
Equipment	1/04/2018	30/04/2018	10000
Labor	1/04/2018	30/03/2021	50000
Service	1/04/2018	30/03/2021	40000

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

Period:	1/04/2018	30/09/2019
Category	Revenue	Revenue
Equipment	10,000.00	-
Labor	1,388.89	1,388.89
Service	1,111.11	1,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?

[Ashish_Mathur]

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?

[v-frfei-msft · Accepted solution]

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
            )
        )
    )

BTW, pbix as attached.

[Keropi79]

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.