Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Dear Team,
I need help in Writing a New Measure to Split/divide/distribute the member premium when it filtered by Date ' Date Table,
the issues is when i filter by the date table it calculte the sum with all amounts based only on the member effcitive date, which is wrong in my example, as I need the DAX in the measure to distribute member premium equaly by days based on the filter duration that i will use , below youll find a screenshot of my data.
Note : I may need a soulation in measurment calculation not in Power Querry as my data is very huge and i can not split it per daye for all records.
Member Effective Date | Member Ineffective Date | Member Premium |
01/01/2022 | 04/07/2022 | 246.51 |
01/01/2022 | 04/07/2022 | 246.51 |
01/01/2022 | 04/07/2022 | 210.718 |
01/01/2022 | 04/07/2022 | 210.718 |
01/01/2022 | 04/07/2022 | 210.718 |
01/01/2022 | 04/07/2022 | 210.718 |
01/01/2022 | 04/07/2022 | 210.718 |
01/01/2022 | 30/09/2022 | 364.405 |
01/01/2022 | 30/09/2022 | 364.405 |
01/01/2022 | 30/09/2022 | 364.405 |
01/01/2022 | 30/09/2022 | 364.405 |
01/01/2022 | 30/09/2022 | 311.496 |
Solved! Go to Solution.
@NawilatiMahmoud , refer if this can help
Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...
Hi, @NawilatiMahmoud
You can try the following methods.
Column:
Total =
CALCULATE ( SUM ( 'Table'[Member Premium] ),
ALLEXCEPT ( 'Table',
'Table'[Member Effective Date],
'Table'[Member Ineffective Date]
)
)
Measure:
Maxdate = IF(MAX('Date'[Date])<=SELECTEDVALUE('Table'[Member Ineffective Date]),MAX('Date'[Date]),SELECTEDVALUE('Table'[Member Ineffective Date]))
Mindate = IF(MIN('Date'[Date])<=SELECTEDVALUE('Table'[Member Effective Date]),SELECTEDVALUE('Table'[Member Effective Date]),MIN('Date'[Date]))
Measure =
Var _datediff=DATEDIFF([Mindate],[Maxdate],DAY)
Return
DIVIDE(SUM('Table'[Total]),_datediff)
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you dear, but the below solution was easier for me, thank you for your reply and support
Another Note : the distribute member premium should be calculated btween the duration of the start and end date, as if i filter the date more than the end date for any members he will be excluded, and if i start my date filter before the start end, the measure should calculate only the days that incloded in the filter.
@NawilatiMahmoud , refer if this can help
Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...
Thank Dear. Amitchandaj,
it solve my issue correctly 🙂
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
18 | |
18 | |
17 | |
16 |
User | Count |
---|---|
28 | |
27 | |
19 | |
15 | |
14 |