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

[NawilatiMahmoud]

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

[v-zhangti]

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.

[NawilatiMahmoud]

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

[NawilatiMahmoud]

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.

[amitchandak · Accepted 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...

[NawilatiMahmoud]

Thank Dear. Amitchandaj,

it solve my issue correctly 🙂