Forum Discussion

Keropi79's avatar
Keropi79
Frequent Visitor
6 years ago
Solved

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?

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    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.

     

3 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Keropi79
      Frequent Visitor
      6 years ago

      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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    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?