Need Dax help to achive extended Warrenty AMT Calculation

[vengadesh_p]

Hi all,

I have data like the image below

Based on that, i want to show a table like below Image

Logic:
I want to split the Warranty amount based on the number of months between the Warranty End Date to Extended Warranty End Date

Ex: For ID1 Warranty AMT is 3600, Number of months Extended Warranty is 36, so 100rs per Month from Extended Warranty Start to End 

Data 

ID	Purchase Date	Warranty End Date	Extended Warranty End Date	Warrenty AMT
ID1	1 January 2021	1 January 2023	1 January 2026	3600
ID2	1 May 2021	1 May 2023	1 May 2026	3600
ID3	1 July 2021	1 July 2023	1 July 2026	3600
ID4	1 July 2021	1 July 2023	1 July 2026	1800

Thanks

[grazitti_sapna]

Hi @vengadesh_p,

To achieve this, you need to create a new table, use below dax to create new table

WarrantySpreadMonthly =

ADDCOLUMNS (

    GENERATE (

        'Data',  -- Replace with your actual table name

        VAR StartDate = 'Data'[Warraty date]

        VAR EndDate = 'Data'[Extended Warranty Date]

        VAR NumMonths = DATEDIFF(StartDate, EndDate, MONTH)

        RETURN

            ADDCOLUMNS (

                GENERATESERIES(1, NumMonths, 1),

                "MonthDate", EDATE(StartDate, [Value])

            )

    ),

    "MonthlyAmount",

        DIVIDE (

            'Data'[Amount],

            DATEDIFF (

                'Data'[Warraty date],

                'Data'[Extended Warranty Date],

                MONTH

            ),

            0

        ),

    "PurchaseMonth", FORMAT('Data'[Purchase date], "mmm-yyyy"),

    "WarrantyEndMonth", FORMAT([MonthDate], "mmm-yyyy"),

    "WarrantyEndMonthSort", YEAR([MonthDate]) * 100 + MONTH([MonthDate])

)

Once done you can add the Purchase date to Row and Warranty end date to column and sum(Amount), Please see the attached example .pbix file.

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[vengadesh_p]

Hi Thanks for your Solution. 

Based on below screenshot, I want to make a little bit of changes to your logic

Thank you once again

[grazitti_sapna · Accepted solution]

Hi @vengadesh_p,

Issue was with the DAX, generateseries function, I've updated the DAX, 

WarrantySpreadMonthly =

ADDCOLUMNS (

    GENERATE (

        'Data',

        VAR StartDate = 'Data'[Warraty date]

        VAR EndDate = 'Data'[Extended Warranty Date]

        VAR NumMonths = DATEDIFF(StartDate, EndDate, MONTH)

        RETURN

            ADDCOLUMNS (

                GENERATESERIES(0, NumMonths - 1, 1),

                "MonthDate", EDATE(StartDate, [Value])

            )

    ),

    "MonthlyAmount",

        DIVIDE (

            'Data'[Amount],

            DATEDIFF('Data'[Warraty date], 'Data'[Extended Warranty Date], MONTH),

            0

        ),

    "PurchaseMonth", FORMAT('Data'[Purchase date], "mmm-yyyy"),

    "WarrantyEndMonth", FORMAT([MonthDate], "mmm-yyyy"),

    "WarrantyEndMonthSort", YEAR([MonthDate]) * 100 + MONTH([MonthDate])

)

It should not show correct values

 

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!