Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
Order Fact:
Order Create date
Financial close date
Date Hierarchy:
Date
Active relationship: Date Hierarchy[Date] = Order Fact[order create date)
Inactive relationship: Date Hierarchy[Date] = Order Fact[fiancial close date)
I want to create the Measure with filter
Calculate ( sum( order amount)),Filter(order fact,
order date < 2023-11-01 && financial close date>=2023-11-01))
I want to work this measure dynamically by user selection of month from date hierarchy
Thanks for your support
Solved! Go to Solution.
Hi @kavimk
The measure I offerd change dynamically, you can choose the month, it will return all the date that meet the condition(include the past period),you can download the pbix I have offered at the 5th message.
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @kavimk
As @amitchandak mentioned, you can use crossfilter() function
You can refer to the following sample.
Sample data
Date table
You can create a measure.
Measure =
IF (
ISFILTERED ( 'Date'[Date].[Month] ),
CALCULATE (
SUM ( 'Order Fact'[order amount] ),
EOMONTH ( 'Order Fact'[Order Create date], 0 )
<= EOMONTH ( MAX ( 'Date'[Date] ), 0 )
&& EOMONTH ( 'Order Fact'[Financial close date], 0 )
>= EOMONTH ( MAX ( 'Date'[Date] ), 0 ),
CROSSFILTER ( 'Date'[Date], 'Order Fact'[Order Create date], NONE )
),
CALCULATE ( SUM ( 'Order Fact'[order amount] ) )
)
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for your support
order Create < 2023-11-01 which means it will give all period 2022,2021,2020 when finacial close date >=2023-11-01
EOMONTH work on specific month not enitre month
Hi @kavimk
You can try the following measure
Measure =
VAR a =
EOMONTH ( MAX ( 'Date'[Date] ), -1 ) + 1
//it will return the first day of the month you have chosen
RETURN
IF (
ISFILTERED ( 'Date'[Date].[Month] ),
CALCULATE (
SUM ( 'Order Fact'[order amount] ),
'Order Fact'[Order Create date] <= a
&& 'Order Fact'[Financial close date] >= a,
CROSSFILTER ( 'Date'[Date], 'Order Fact'[Order Create date], NONE )
),
CALCULATE ( SUM ( 'Order Fact'[order amount] ) )
)
And EOMONTH() means it will return the last day of the month, not the specific month, it wll also return the entire month, EOMONTH(date[date],0) means it will return all the last dates of the months of each date, You can see the following picture.
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for response Expected result is,
Measure=
Hi @kavimk
The measure I offerd change dynamically, you can choose the month, it will return all the date that meet the condition(include the past period),you can download the pbix I have offered at the 5th message.
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@kavimk , Have tried using userelationship to create the measure
Calculate ( sum( order amount)), userelationship('Date Hierarchy'[Date] , 'Order Fact'[fiancial close date]))
example
HR Analytics- Hired, terminated, and active employees using UserRelationships and crossfilter: https://youtu.be/K14OPlGc9E8
User | Count |
---|---|
94 | |
80 | |
76 | |
65 | |
60 |
User | Count |
---|---|
108 | |
101 | |
77 | |
63 | |
61 |