Sales on segment dates

[Nibz]

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 try to write this formula in Power BI

[BillSales] 3 * (Difference in days between (

                               (IF [Bill.EndDate] 5 - 1 day > 2 ; 2 ; [Bill.EndDate] 5 - 1 day)

                               AND

                               (IF [Bill.StartDate] 4 > [SegmentFilter.StartDate] 1; [SegmentFilter.StartDate] 1; [Bill.StartDate] 4 )

                ) + 1 day

) / (Difference in days between [Bill.EndDate] 5 and [Bill.StartDate] 4 + 1 day)

Many thanks for your help

[ibarrau]

Hi, the answer may contain error because I am not sure I understand everything you say. Anyway, let's see.

Asuming you have two tables
- Table -> the one with slicer
- BillTable -> the one with bills

First Create this Measures for the table containing the Slicer:

MAXDate = CALCULATE(MAX(Table[SlicerDate]); VALUES(Table[SlicerDate]))
MINDate = CALCULATE(MIN(Table[SlicerDate]); VALUES(Table[SlicerDate]))

Then create a column for BillSales like this
=
BillTable[BillSales]
    * (
        (
            DATEDIFF (
                IF (
                    DATEADD ( BillTable[Bill.EndDate], -1, DAY ) > [MAXDate],
                    [MAXDate],
                    DATEADD ( BillTable[Bill.EndDate], -1, DAY )
                ),
                IF (
                    BillTable[Bill.StartDate] > BillTable[SegmentFilter.StartDate],
                    BillTable[SegmentFilter.StartDate],
                    BillTable[Bill.StartDate]
                ),
                DAY
            )
                + 1
        )
            / DATEDIFF (
                BillTable[Bill.EndDate],
                DATEADD ( BillTable[Bill.StartDate], 1, DAY ),
                DAY
            )
    )

I hope that helps you. If it doesn't work at least you know more about dax now 😛

Regards,

[Nibz]

I think we are really close to do it.

I have two problems.

1) I am not able to use DATEADD formula

I don't know why but Power BI don't show me this formula

2) The functuion calculate is not allowed in multiply formula in direct query

I have write the formula below

Sales = BillItem[Montant HT] * (
                                    (
                                        DATEDIFF(
                                            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
                                            );
                                            IF(
                                            BillItem[Start Date] > [MinDate];
                                            [MinDate];
                                            BillItem[Start Date]
                                            );
                                            DAY
                                        )
                                            +1
                                    )
                                        / DATEDIFF(
                                            BillItem[End Date];
                                            DATE(YEAR(BillItem[Start Date]);MONTH(BillItem[Start Date]);DAY(BillItem[Start Date]))+1;
                                            DAY
                                        )
                                )

And then, I have the error

"The CALCULATE function is not allowed as part of the calculated expressions of the DAX column in the DirectQuery templates"

[v-ljerr-msft]

Hi @Nibz,

Could you try the formula below to see if it works in your scenario?

Sales =
VAR maxDate =
    MAX ( Table[SlicerDate] )
VAR minDate =
    MIN ( Table[SlicerDate] )
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
                )
        )

Note: Make sure you have turned on File | Options and settings | Options | Direct Query | Allow unrestricted measures in Direct Query mode.

Regards

[Nibz]

Hi,

Unfortunately, I don't have access to VAR and I don't know why.

I confirm I allow unrestricted measurs in Direct Query mode

[v-ljerr-msft]

Hi @Nibz,

Never mind the IntelliSense(I can see the same on my side), just input VAR then enter "Space" and finish the formula, it should work.

Regards

[Nibz]

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]

Hi @Nibz,

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

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]

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]

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]

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

[Nibz]

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