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.
Hi Experts ,
I am new to power bi and need help on the same on the calculation of relative filters between 2 or more date fields and show Sales Amount
Sum of Sales when
1.Effective date within the date range selected(inclusive) and the Entry date is less than financial closing date for End of range selected
2. Effective date prior to the selection start date and entry date between financial opening date for Start of range selected and financial closing date for end of range selected(inclusive).
All dates are available in a single view .Hope someone helps me on the same .
Regards ,
Sunny
Solved! Go to Solution.
Hi @sunny1284 ,
It is very difficult to analyze without looking at the data and just by imagining. Based on your description, you could create measures to calculate data within the date range. You can refer to the DAX below:
Measure =
CALCULATE (
SUM ( table[SalesAmount] ),
FILTER ( table,
table[Effective date] <= MAX ( table[Date] ) &&
table[Effective date] >= MIN (table[Date]) &&
table[Entry date]<= table[financial closing date]
)
)
if this is not the result you want, please provide some sample data or paste pictures, so that we can help you solve the problem.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @sunny1284 ,
It is very difficult to analyze without looking at the data and just by imagining. Based on your description, you could create measures to calculate data within the date range. You can refer to the DAX below:
Measure =
CALCULATE (
SUM ( table[SalesAmount] ),
FILTER ( table,
table[Effective date] <= MAX ( table[Date] ) &&
table[Effective date] >= MIN (table[Date]) &&
table[Entry date]<= table[financial closing date]
)
)
if this is not the result you want, please provide some sample data or paste pictures, so that we can help you solve the problem.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |