topic Re: How To Write a New Measure to Split/divide/distribute a value when it filtered by Date ' Date Table in DAX Commands and Tips

How To Write a New Measure to Split/divide/distribute a value when it filtered by Date ' Date Table

NawilatiMahmoud — Mon, 02 Jan 2023 21:59:01 GMT

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

Re: How To Write a New Measure to Split/divide/distribute a value when it filtered by Date ' Date Table

NawilatiMahmoud — Mon, 02 Jan 2023 22:16:32 GMT

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.

Re: How To Write a New Measure to Split/divide/distribute a value when it filtered by Date ' Date Table

amitchandak — Tue, 03 Jan 2023 01:20:59 GMT

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-or-end-date/ba-p/1503785

Re: How To Write a New Measure to Split/divide/distribute a value when it filtered by Date ' Date Table

v-zhangti — Tue, 03 Jan 2023 09:13:56 GMT

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.

Re: How To Write a New Measure to Split/divide/distribute a value when it filtered by Date ' Date Table

NawilatiMahmoud — Wed, 04 Jan 2023 05:51:28 GMT

Thank Dear. Amitchandaj,

it solve my issue correctly 🙂

Re: How To Write a New Measure to Split/divide/distribute a value when it filtered by Date ' Date Table

NawilatiMahmoud — Wed, 04 Jan 2023 05:53:02 GMT

Thank you dear, but the below solution was easier for me, thank you for your reply and support