Dax Formula to find out Monthly value from given date range

[msk_muthu]

Hi Team,

Need your help to crack the formula to find out the amount for each month with the given date range

for eg.:
From Date is 05-11-2021,
To date is 06-01-2022,
Invoice amount is 10800,
Total No.of Days are 63,
Per Day Amount is 171,
There will be 12 Calculated column for each month from April to October the value has to reflect 0
for November month value has to reflect for 26 days * per Day Amount
for December month value has to reflect for 31 days * Per Day Amount
for January month value has to reflect for 6 days * Per Day Amount

Please find the attachment and help me to get formulas, Also the date range includes leap year

FROM Date	To Date	Invoice Amount	CC_Activity_No_of_Days	CC_PerDayInvoiceAmount	CC_April	CC_May	CC_June	CC_July	CC_August	CC_September	CC_October	CC_November	CC_December	CC_January	CC_February	CC_March	Total
20-Mar-24	02-Apr-24	3,240.00	14	231.43	462.86	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	2,777.14	3,240.00
11-Apr-24	24-Apr-24	8,287.99	14	592.00	8,287.99	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	8,287.99
05-Nov-21	06-Jan-22	10,800.00	63	171.43	0.00	0.00	0.00	0.00	0.00	0.00	0.00	4457.14	5314.29	1028.57	0.00	0.00	10,800.00
05-Dec-22	01-Mar-23	11,500.00	87	132.18	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	3,568.97	4,097.70	3,701.15	132.18	11,500.00
15-Nov-23	05-Mar-24	18,600.00	112	166.07	0.00	0.00	0.00	0.00	0.00	0.00	0.00	2,657.14	5,148.21	5,148.21	4,816.07	830.36	18,600.00

Thank you
MSKarthikeyan

[divyed · Accepted solution]

Hello @msk_muthu ,

Here is a dax giving same data you want as per sample given. This is dynamic and can run on any data provided.

//Idea was to achieve expected output and hence did not focus on formatting and optimization. I know you will do as needed

// This is not the best code honestly but will work on any data in given format. you can create variables and pass as per your need to simplyfy the query 

FinalTable =

ADDCOLUMNS(

    Table1,

    "Number_of_days",

        VAR TotalDays = DATEDIFF([From_Date]-1, [To_Date], DAY)

        RETURN TotalDays,

    "Amount_per_Day",

        VAR AmountPerDay = DIVIDE([Invoice_Amount], DATEDIFF([From_Date]-1, [To_Date], DAY), 0)

        RETURN AmountPerDay,

    "January",

        VAR StartDateJan = MAX(DATE(YEAR([From_Date]), 1, 1), [From_Date])

        VAR EndDateJan = MIN(DATE(YEAR([From_Date]), 1, 31), [To_Date])

        VAR DaysInJan = DATEDIFF(StartDateJan, EndDateJan, DAY) + 1

        RETURN IF(DaysInJan > 0, ( DaysInJan * DIVIDE([Invoice_Amount], DATEDIFF([From_Date]-1, [To_Date], DAY), 0)), 0),

    "February",

        VAR StartDateFeb = MAX(DATE(YEAR([From_Date]), 2, 1), [From_Date])

        VAR EndDateFeb = MIN(DATE(YEAR([From_Date]), 2, 28), [To_Date])

        VAR DaysInFeb = DATEDIFF(StartDateFeb, EndDateFeb, DAY) + 1

        RETURN IF(DaysInFeb > 0, (DaysInFeb * DIVIDE([Invoice_Amount], DATEDIFF([From_Date]-1, [To_Date], DAY), 0)), 0),

    "March",

        VAR StartDateMar = MAX(DATE(YEAR([From_Date]), 3, 1), [From_Date])

        VAR EndDateMar = MIN(DATE(YEAR([From_Date]), 3, 31), [To_Date])

        VAR DaysInMar = DATEDIFF(StartDateMar, EndDateMar, DAY) + 1

        RETURN IF(DaysInMar > 0, (DaysInMar * DIVIDE([Invoice_Amount], DATEDIFF([From_Date]-1, [To_Date], DAY), 0)), 0),

    "April",

        VAR StartDateApr = MAX(DATE(YEAR([From_Date]), 4, 1), [From_Date])

        VAR EndDateApr = MIN(DATE(YEAR([From_Date]), 4, 30), [To_Date])

        VAR DaysInApr = DATEDIFF(StartDateApr, EndDateApr, DAY) + 1

        RETURN IF(DaysInApr > 0, (DaysInApr * DIVIDE([Invoice_Amount], DATEDIFF([From_Date]-1, [To_Date], DAY), 0)), 0),

    "May",

        VAR StartDateMay = MAX(DATE(YEAR([From_Date]), 5, 1), [From_Date])

        VAR EndDateMay = MIN(DATE(YEAR([From_Date]), 5, 31), [To_Date])

        VAR DaysInMay = DATEDIFF(StartDateMay, EndDateMay, DAY) + 1

        RETURN IF(DaysInMay > 0, (DaysInMay * DIVIDE([Invoice_Amount], DATEDIFF([From_Date]-1, [To_Date], DAY), 0)), 0),

    "June",

        VAR StartDateJun = MAX(DATE(YEAR([From_Date]), 6, 1), [From_Date])

        VAR EndDateJun = MIN(DATE(YEAR([From_Date]), 6, 30), [To_Date])

        VAR DaysInJun = DATEDIFF(StartDateJun, EndDateJun, DAY) + 1

        RETURN IF(DaysInJun > 0, (DaysInJun * DIVIDE([Invoice_Amount], DATEDIFF([From_Date]-1, [To_Date], DAY), 0)), 0),

    "July",

        VAR StartDateJul = MAX(DATE(YEAR([From_Date]), 7, 1), [From_Date])

        VAR EndDateJul = MIN(DATE(YEAR([From_Date]), 7, 31), [To_Date])

        VAR DaysInJul = DATEDIFF(StartDateJul, EndDateJul, DAY) + 1

        RETURN IF(DaysInJul > 0, (DaysInJul * DIVIDE([Invoice_Amount], DATEDIFF([From_Date]-1, [To_Date], DAY), 0)), 0),

    "August",

        VAR StartDateAug = MAX(DATE(YEAR([From_Date]), 8, 1), [From_Date])

        VAR EndDateAug = MIN(DATE(YEAR([From_Date]), 8, 31), [To_Date])

        VAR DaysInAug = DATEDIFF(StartDateAug, EndDateAug, DAY) + 1

        RETURN IF(DaysInAug > 0, (DaysInAug * DIVIDE([Invoice_Amount], DATEDIFF([From_Date]-1, [To_Date], DAY), 0)), 0),

    "September",

        VAR StartDateSep = MAX(DATE(YEAR([From_Date]), 9, 1), [From_Date])

        VAR EndDateSep = MIN(DATE(YEAR([From_Date]), 9, 30), [To_Date])

        VAR DaysInSep = DATEDIFF(StartDateSep, EndDateSep, DAY) + 1

        RETURN IF(DaysInSep > 0, (DaysInSep * DIVIDE([Invoice_Amount], DATEDIFF([From_Date]-1, [To_Date], DAY), 0)), 0),

    "October",

        VAR StartDateOct = MAX(DATE(YEAR([From_Date]), 10, 1), [From_Date])

        VAR EndDateOct = MIN(DATE(YEAR([From_Date]), 10, 31), [To_Date])

        VAR DaysInOct = DATEDIFF(StartDateOct, EndDateOct, DAY) + 1

        RETURN IF(DaysInOct > 0, (DaysInOct * DIVIDE([Invoice_Amount], DATEDIFF([From_Date]-1, [To_Date], DAY), 0)), 0),

    "November",

        VAR StartDateNov = MAX(DATE(YEAR([From_Date]), 11, 1), [From_Date])

        VAR EndDateNov = MIN(DATE(YEAR([From_Date]), 11, 30), [To_Date])

        VAR DaysInNov = DATEDIFF(StartDateNov, EndDateNov, DAY) + 1

        RETURN IF(DaysInNov > 0, (DaysInNov * DIVIDE([Invoice_Amount], DATEDIFF([From_Date]-1, [To_Date], DAY), 0)), 0),

    "December",

        VAR StartDateDec = MAX(DATE(YEAR([From_Date]), 12, 1), [From_Date])

        VAR EndDateDec = MIN(DATE(YEAR([From_Date]), 12, 31), [To_Date])

        VAR DaysInDec = DATEDIFF(StartDateDec, EndDateDec, DAY) + 1

        RETURN IF(DaysInDec > 0, (DaysInDec * DIVIDE([Invoice_Amount], DATEDIFF([From_Date]-1, [To_Date], DAY), 0)), 0)

)

Did I answer your query ? Please mark this as solution if this helps. Always love your Kudos 🙂

Cheers

https://www.linkedin.com/in/neeraj-kumar-62246b26/

[sanalytics]

@msk_muthu 

Can you provide some more aspect with some dummy data and your exact output and the formula concept.So that we can help you to build the DAX formula.

Regards

sanalytics

If it is your solution then please like and accept it as solution