dates segment filter

[Nibz]

I try to calculate sales regarding two dates in a segment filter.

I am in Direct Query

The formula I try to do is :

[BillSales] * (Difference in days between (

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

                               AND

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

                ) + 1 day

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

If somebody can help me to write this formula in DAX, I will appreciate.

Thank you

[v-sihou-msft]

@Nibz

In this scenario, if your Bill Table and SegmentFilter Table is one-to-one mapping, you can create a calculated column for your logic.

Column =
Bill[Sales]
    * (
        1
            * (
                IF (
                    ( Bill[EndDate] - 1 )
                        > SegmentFilter[EndDate],
                    SegmentFilter[EndDate],
                    ( Bill[EndDate] - 1 )
                )
                    - IF (
                        ( Bill[StartDate] - 1 )
                            > SegmentFilter[StartDate],
                        SegmentFilter[StartDate],
                        ( Bill[StartDate] - 1 )
                    )
            )
            + 1
    )
    / ( 1
    * ( Bill[EndDate] - Bill[StartDate] )
    + 1 )

Regards,

[Nibz]

Thanks a lot for your help.

But I think one of my point was not clear.

Actually, It is not two tables. SegmentFilter is not a table but a date (column StartDate from BillTable) and I use it like a filter in my report, in a segment graph.

So I would like that my formula is based on this segment.