Forum Discussion

Nibz's avatar
Nibz
Helper 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 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

Need Help

11 Replies

  • ibarrau's avatar
    ibarrau
    Super User
    9 years ago

    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 :P

    Regards,

    • Nibz's avatar
      Nibz
      Helper I
      9 years ago

      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's avatar
        v-ljerr-msft
        Microsoft Employee
        9 years ago

        Hi Nibz,

         

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

         

        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