Forum Discussion

Nibz's avatar
Nibz
Icon for Helper I rankHelper I
9 years ago

Sales on segment dates

In a specific table, I have a date, and I use it in a segment graph   Then I have an other table with the following information : [BillSales] 3 [Bill.StartDate] 4 [Bill.EndDate] 5   And I ...
Need Help
Nibz's avatar
Nibz
Icon for Helper I rankHelper I
9 years ago

Ok thanks.

 

I have done the same formula but know it says

 

"It is impossible to determine a single value for the culumn "MontantHT" of the table "BillItem". This can occur when a measurement formula refers to a column that countains many values, whithout specifying aggregation such as min, max, count, or sum to get a single result."

 

My formula :

 

Mesure 2 =
VAR maxDate =
    MAX('Date'[Date])
VAR minDate =
    MIN('Date'[Date])
RETURN
    BillItem[Montant HT]
        * (
            (
                DATEDIFF (
                    IF ( BillItem[Start Date] > minDate; minDate; BillItem[Start Date] );
                    IF (
                        (
                            DATE ( YEAR ( BillItem[End Date] ); MONTH ( BillItem[End Date] ); DAY ( BillItem[End Date] ) )
                                - 1
                        )
                            > maxDate;
                        maxDate;
                        DATE ( YEAR ( BillItem[End Date] ); MONTH ( BillItem[End Date] ); DAY ( BillItem[End Date] ) )
                            - 1
                    );
                    DAY
                )
                    + 1
            )
                / DATEDIFF (
                    DATE ( YEAR ( BillItem[Start Date] ); MONTH ( BillItem[Start Date] ); DAY ( BillItem[Start Date] ) )
                        + 1;
                    BillItem[End Date];
                    DAY
                )
        )

 

 

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi Nibz,

 

As indicated in the message, try adding SUM function for the column "MontantHT" to see if it works.:smileyhappy:

 

Mesure 2 =
VAR maxDate =
    MAX ( 'Date'[Date] )
VAR minDate =
    MIN ( 'Date'[Date] )
RETURN
    SUM ( BillItem[Montant HT] )
        * (
            (
                DATEDIFF (
                    IF ( BillItem[Start Date] > minDate; minDate; BillItem[Start Date] );
                    IF (
                        (
                            DATE ( YEAR ( BillItem[End Date] ); MONTH ( BillItem[End Date] ); DAY ( BillItem[End Date] ) )
                                - 1
                        )
                            > maxDate;
                        maxDate;
                        DATE ( YEAR ( BillItem[End Date] ); MONTH ( BillItem[End Date] ); DAY ( BillItem[End Date] ) )
                            - 1
                    );
                    DAY
                )
                    + 1
            )
                / DATEDIFF (
                    DATE ( YEAR ( BillItem[Start Date] ); MONTH ( BillItem[Start Date] ); DAY ( BillItem[Start Date] ) )
                        + 1;
                    BillItem[End Date];
                    DAY
                )
        )

 

Regards

  • Nibz's avatar
    Nibz
    Icon for Helper I rankHelper I
    9 years ago

    Now I have the same message but for StartDate...

     

    "It is impossible to determine a single value for the culumn "Start Date" of the table "BillItem". This can occur when a measurement formula refers to a column that countains many values, whithout specifying aggregation such as min, max, count, or sum to get a single result."

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Nibz,

     

    How about using the formula below?

     

    Mesure 2 =
VAR maxDate =
    MAX ( 'Date'[Date] )
VAR minDate =
    MIN ( 'Date'[Date] )
RETURN
    SUMX (
        'BillItem';
        BillItem[Montant HT]
            * (
                (
                    DATEDIFF (
                        IF ( BillItem[Start Date] > minDate; minDate; BillItem[Start Date] );
                        IF (
                            (
                                DATE ( YEAR ( BillItem[End Date] ); MONTH ( BillItem[End Date] ); DAY ( BillItem[End Date] ) )
                                    - 1
                            )
                                > maxDate;
                            maxDate;
                            DATE ( YEAR ( BillItem[End Date] ); MONTH ( BillItem[End Date] ); DAY ( BillItem[End Date] ) )
                                - 1
                        );
                        DAY
                    )
                        + 1
                )
                    / DATEDIFF (
                        DATE ( YEAR ( BillItem[Start Date] ); MONTH ( BillItem[Start Date] ); DAY ( BillItem[Start Date] ) )
                            + 1;
                        BillItem[End Date];
                        DAY
                    )
            )
    )

     

    Regards

  • Nibz's avatar
    Nibz
    Icon for Helper I rankHelper I
    9 years ago

    I think we are close :)

     

    The formula is accepted but it is impossible to load data.

    In the function DATEDIFF, Start date cannot be after End Date...

  • Nibz's avatar
    Nibz
    Icon for Helper I rankHelper I
    9 years ago

    I think the problem is

     

    DATEDIFF (
                        DATE ( YEAR ( BillItem[Start Date] ); MONTH ( BillItem[Start Date] ); DAY ( BillItem[Start Date] ) )
                            + 1;
                        BillItem[End Date];
                        DAY

    the +1 has to be add to the total difference