Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
NawilatiMahmoud
Frequent Visitor

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

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 DateMember Ineffective DateMember Premium
 01/01/2022 04/07/2022246.51
 01/01/2022 04/07/2022246.51
 01/01/2022 04/07/2022210.718
 01/01/2022 04/07/2022210.718
 01/01/2022 04/07/2022210.718
 01/01/2022 04/07/2022210.718
 01/01/2022 04/07/2022210.718
 01/01/2022 30/09/2022364.405
 01/01/2022 30/09/2022364.405
 01/01/2022 30/09/2022364.405
 01/01/2022 30/09/2022364.405
 01/01/2022 30/09/2022311.496
1 ACCEPTED SOLUTION
5 REPLIES 5
v-zhangti
Community Support
Community Support

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]
    )
)

vzhangti_0-1672737046852.png

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)

vzhangti_1-1672737141180.pngvzhangti_2-1672737168315.png

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

NawilatiMahmoud
Frequent Visitor

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.

Thank Dear. Amitchandaj,

 

it solve my issue correctly 🙂

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors