Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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,
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |