Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
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,
Happy to help!
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"
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
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
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
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
)
)
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
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."
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
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
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...
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
84 | |
66 | |
62 | |
59 |
User | Count |
---|---|
197 | |
118 | |
108 | |
78 | |
69 |