Solved: Rolling 12 months (From Earliest_Use_date) + sum o...

MCosta_BI · ‎07-18-2023

Hello,

I was wondering if anyone can help me with a Dax measure for Rolling 12 months for a commission plan structure. Sales reps are paid based on the rolling 12 months of revenue produced by customers that start on Earliest_use_date that ends 11 months after.

here is the structure

Customer	Approved_date	Earliest_Use_Date	R12 months Revenue	Monthly Fee Prior to Activation
Maria_123	2022-01-01	2022-05-05	Revenue generated from= Start_Month Earliest_Use_Date + 11 months	paid only if activated
Jose_124	2023-02-01	2023-05-15	Revenue generated from= Start_Month Earliest_Use_Date + 11 months	paid only if activated
Carlos_211	2021-07-10	2022-01-12	Revenue generated from= Start_Month Earliest_Use_Date + 11 months	paid only if activated

Totals for each should be as follow

Customers	Commissionable total	Notes
Maria_123	413.00
Jose_124	90.00	still to be calculated revenue for months Aug 2023- April 2024
Carlos_211	240.00

Sample file

Customer	Revenue Date	Total Revenue	R12 M Revenue	Monthly Fee Prior to Activation	Commission Monthly
Maria_123	2022-01-31	10.00
Maria_123	2022-02-28	10.00
Maria_123	2022-03-31	10.00
Maria_123	2022-04-30	10.00
Maria_123	2022-05-31	30.00	30.00	40.00	70.00
Maria_123	2022-06-30	32.00	32.00		32.00
Maria_123	2022-07-31	35.00	35.00		35.00
Maria_123	2022-08-31	36.00	36.00		36.00
Maria_123	2022-09-30	30.00	30.00		30.00
Maria_123	2022-10-31	30.00	30.00		30.00
Maria_123	2022-11-30	30.00	30.00		30.00
Maria_123	2022-12-31	30.00	30.00		30.00
Maria_123	2023-01-31	30.00	30.00		30.00
Maria_123	2023-02-28	30.00	30.00		30.00
Maria_123	2023-03-31	30.00	30.00		30.00
Maria_123	2023-04-30	30.00	30.00		30.00
Maria_123	2023-05-31	30.00
Maria_123	2023-06-30	30.00
Maria_123	2023-07-31	30.00
Maria_123	2023-08-31	30.00
Jose_124	2023-02-28	10.00
Jose_124	2023-03-31	10.00
Jose_124	2023-04-30	10.00
Jose_124	2023-05-31	20.00	20.00	30.00	50.00
Jose_124	2023-06-30	20.00	20.00		20.00
Jose_124	2023-07-31	20.00	20.00		20.00
Carlos_211	2021-07-31	10.00
Carlos_211	2021-08-31	10.00
Carlos_211	2021-09-30	10.00
Carlos_211	2021-10-31	10.00
Carlos_211	2021-11-30	10.00
Carlos_211	2021-12-31	10.00
Carlos_211	2022-01-31	15.00	15.00	60.00	75.00
Carlos_211	2022-02-28	15.00	15.00		15.00
Carlos_211	2022-03-31	15.00	15.00		15.00
Carlos_211	2022-04-30	15.00	15.00		15.00
Carlos_211	2022-05-31	15.00	15.00		15.00
Carlos_211	2022-06-30	15.00	15.00		15.00
Carlos_211	2022-07-31	15.00	15.00		15.00
Carlos_211	2022-08-31	15.00	15.00		15.00
Carlos_211	2022-09-30	15.00	15.00		15.00
Carlos_211	2022-10-31	15.00	15.00		15.00
Carlos_211	2022-11-30	15.00	15.00		15.00
Carlos_211	2022-12-31	15.00	15.00		15.00
Carlos_211	2023-01-31	15.00
Carlos_211	2023-02-28	15.00
Carlos_211	2023-03-31	15.00
Carlos_211	2023-04-30	15.00
Carlos_211	2023-05-31	15.00
Carlos_211	2023-06-30	15.00
Carlos_211	2023-07-31	15.00

Ashish_Mathur · ‎07-21-2023

Hi,

Please find attached the PBI file.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

MCosta_BI · ‎07-18-2023

@Ashish_Mathur thank you very much. I don't originally have the column that sums up the "monthly fee prior to activation". I need a Dax for that as well. I am sorry I wasn't clear on my first message. All I have is the columns Customer , Revenue Date and total revenue.

Ashish_Mathur · ‎07-18-2023

You are welcome. Then how does one know when the revenue accumulation should start from?

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

MCosta_BI · ‎07-18-2023

On my Customer table i have the following three columns: customer, Earliest_Use_Date, Approved_Date

So my intention is to use these dates to calculate the rolling 12m of Revenue + Monthly Fee Prior to Activation.

The rolling 12m :

Start_Date= the month of Earliest_Use_Date

End_Date = 11 months rolling after Earliest_Use_Date

Monthly Fee Prior to Activation :

Start Date = the month of approved_date

End_Date = the month of Earliest_Use_Date-1

Ashish_Mathur · ‎07-18-2023

Share the raw data (in a format that can be pasted in an MS Excel file) and this time check your message thoroughly before you post.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

MCosta_BI · ‎07-20-2023

I am so sorry @Ashish_Mathur . Your are right! I should have been more clear from start.

Customer Table

customer_number_	Name_	Created_date_	Approved_date_	Earliest_Use	Activated Services	AccountManager_
16217	Maria_123	2021-12-25	2022-01-01	2022-05-05	yes	Jake
33588	Jose_124	2022-04-28	2023-02-01	2023-05-15	yes	Frank
28580	Carlos_211	2021-06-19	2021-07-10	2022-01-12	yes	Colin
24359	ABC24381	2022-04-20	2022-04-22	2022-06-16	yes	Colin
25476	ABC25498	2022-01-09	2022-04-22	2022-07-14	yes	Jake
23265	ABC23287	2022-03-02	2022-04-07		no	Lucia

Transactions Table

customer_number_	Name	Revenue Date	Total Revenue
16217	Maria_123	2022-01-31	10.00
16217	Maria_123	2022-02-28	10.00
16217	Maria_123	2022-03-31	10.00
16217	Maria_123	2022-04-30	10.00
16217	Maria_123	2022-05-31	30.00
16217	Maria_123	2022-06-30	32.00
16217	Maria_123	2022-07-31	35.00
16217	Maria_123	2022-08-31	36.00
16217	Maria_123	2022-09-30	30.00
16217	Maria_123	2022-10-31	30.00
16217	Maria_123	2022-11-30	30.00
16217	Maria_123	2022-12-31	30.00
16217	Maria_123	2023-01-31	30.00
16217	Maria_123	2023-02-28	30.00
16217	Maria_123	2023-03-31	30.00
16217	Maria_123	2023-04-30	30.00
16217	Maria_123	2023-05-31	30.00
16217	Maria_123	2023-06-30	30.00
16217	Maria_123	2023-07-31	30.00
16217	Maria_123	2023-08-31	30.00
33588	Jose_124	2023-02-28	10.00
33588	Jose_124	2023-03-31	10.00
33588	Jose_124	2023-04-30	10.00
33588	Jose_124	2023-05-31	20.00
33588	Jose_124	2023-06-30	20.00
33588	Jose_124	2023-07-31	20.00
28580	Carlos_211	2021-07-31	10.00
28580	Carlos_211	2021-08-31	10.00
28580	Carlos_211	2021-09-30	10.00
28580	Carlos_211	2021-10-31	10.00
28580	Carlos_211	2021-11-30	10.00
28580	Carlos_211	2021-12-31	10.00
28580	Carlos_211	2022-01-31	15.00
28580	Carlos_211	2022-02-28	15.00
28580	Carlos_211	2022-03-31	15.00
28580	Carlos_211	2022-04-30	15.00
28580	Carlos_211	2022-05-31	15.00
28580	Carlos_211	2022-06-30	15.00
28580	Carlos_211	2022-07-31	15.00
28580	Carlos_211	2022-08-31	15.00
28580	Carlos_211	2022-09-30	15.00
28580	Carlos_211	2022-10-31	15.00
28580	Carlos_211	2022-11-30	15.00
28580	Carlos_211	2022-12-31	15.00
28580	Carlos_211	2023-01-31	15.00
28580	Carlos_211	2023-02-28	15.00
28580	Carlos_211	2023-03-31	15.00
28580	Carlos_211	2023-04-30	15.00
28580	Carlos_211	2023-05-31	15.00
28580	Carlos_211	2023-06-30	15.00
28580	Carlos_211	2023-07-31	15.00
23265	ABC23287	2022-04-30	10.00
23265	ABC23287	2022-05-31	10.00
23265	ABC23287	2022-06-30	10.00
23265	ABC23287	2022-07-31	10.00
23265	ABC23287	2022-08-31	10.00
23265	ABC23287	2022-09-30	10.00
23265	ABC23287	2022-10-31	10.00
23265	ABC23287	2022-11-30	10.00
23265	ABC23287	2022-12-31	10.00
23265	ABC23287	2023-01-31	10.00
23265	ABC23287	2023-02-28	10.00
23265	ABC23287	2023-03-31	10.00
23265	ABC23287	2023-04-30	10.00
23265	ABC23287	2023-05-31	10.00
23265	ABC23287	2023-06-30	10.00

I have four calendar Dates connected to each specific dates in my customer table. I use them for multiple scenarios of reports.
for Create Date
for Approved Date
for Earliest Use date

for Revenue

I used these two DAX below that calculates the Rolling 12 months of Revenue (Rolling starts on Earliest Use Date and counts 12 months after)

** This Dax helped me to achieve the rolling sumed monthly correctly, but for some reason was omiting some values for Year total so I created the next Dax to force it to sum all.

Rolling 12 using Activation_date ( commissionable) = 
VAR CurrentDate =
    Max('dCalendar'[Date] )
VAR StartDate = CurrentDate - 365
RETURN
    CALCULATE (
       [Total Revenue excl.Setup (for_commission)],
        REMOVEFILTERS(  'dCalendar (EarliestUse)'[Date] ),
        'dCalendar (EarliestUse)'[Date] > StartDate,
        'dCalendar (EarliestUse)'[Date] <= CurrentDate
    )

This is calculating the correct values by month and Year.

m_Rolling 12 months ( Activation) = 
VAR __table = SUMMARIZE('dCalendar',[Date],"__value",[Rolling 12 using Activation_date ( commissionable)])
RETURN
IF(HASONEVALUE('dCalendar'[Date]),[Total Revenue excl.Setup (for_commission)],SUMX(__table,[__value]))

This is how I have data shown by Fiscal Year and month

The problem is that now, the company wants to add any revenue generated by customers before earliest_ use_date. The totals should sum the rolling and the sum of the amount generated prior. The revenue produced before earliest_use date will be summed and paid on the month of earliest_use date + any revenue produced on that month.
I am breaking my head to achieve this.

If you cant help, I will understand. I you think there is light to these dark path, please share with me. I will be forever greatfull.

Thanks,

Munique

Ashish_Mathur · ‎07-20-2023

Hi,

Based on the 2 tables that you have shared, show the expected result very clearly with an explanation.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

MCosta_BI · ‎07-20-2023

This is my expected results:

			2022					2023													2024
AccountManager_	customer_number_	Name	Jan	Feb	Mar	Apr	Total	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	Total	May	Jun	Total
Colin	16217	Carlos_211	75	15	15	15	120	15	15	15	15	15	15	15	15	15	15	15	15	180
Frank	23265	Jose_124																			50	20	70
Jake	28580	Maria_123						70	32	35	36	30	30	30	30	30	30	30	30	413
Grand Total			75	15	15	15	120	85	47	50	51	45	45	45	45	45	45	45	45	593	50	20	70

Original table

table with formulas below

I need to sum the values generated prior to earliest_date on the of activation month.

To look like this :

This should be my end result for all of the headache.

The table "Total revenue 12m rolling from Earliest_Use_Date" I used these two formulas ( as mentioned before).

Rolling 12 using Activation_date ( commissionable) = 
VAR CurrentDate =
    Max('dCalendar (Revenue)'[Calendar Date])
VAR StartDate = CurrentDate - 365
RETURN
    CALCULATE (
       [Total Revenue],
        'dCalendar (EarliestUse)'[Calendar Date] > StartDate,
        'dCalendar (EarliestUse)'[Calendar Date] <= CurrentDate
    )

m_Rolling 12 months ( Activation) = 
VAR __table = SUMMARIZE('dCalendar (Revenue)',[Calendar Date],"__value",[Rolling 12 using Activation_date ( commissionable)])
RETURN
IF(HASONEVALUE('dCalendar (Revenue)'[Calendar Date]),[Total Revenue],SUMX(__table,[__value]))

date table

parameters:

Fiscal Start Month 05

Start Year 2019

let
 Date = let
Source = List.Dates,
FiscalMonthCalc = 12-#"Fiscal Start Month",
NumberofDays= Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(#"Start Year",1,1)),

// 1. Uncomment this line if you want to get your end date being Today's Date +1 day of the Refresh
#"Invoked FunctionSource" = Source(#date(#"Start Year", 1, 1), NumberofDays+1, #duration(1, 0, 0, 0)),

// 2. Uncomment this line below if you want your end date to be 4 years into the future
//#"Invoked FunctionSource" = Source(#date(#"Start Year", 1, 1), Duration.Days(DateTime.Date(Date.AddDays(Date.EndOfYear(Date.AddYears(DateTime.FixedLocalNow(),+0)),1)) - #date(#"Start Year",1,1)), #duration(1, 0, 0, 0)),

// 3. Uncomment this line if you want to get your end date being the Parameter called "End Year"
//#"Invoked FunctionSource" = Source(#date(#"Start Year", 1, 1), Duration.Days(#date(#"End Year", 12, 30) - #date(#"Start Year",1,1)), #duration(1, 0, 0, 0)),

#"Table from List" = Table.FromList(#"Invoked FunctionSource", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Index" = Table.AddIndexColumn(#"Table from List", "Index", 1, 1),
#"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Column1", "Date"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Month Number", each Date.Month([Date])),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Day", each Date.Day([Date])),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Day Name", each Date.ToText([Date],"ddd")),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Month Name", each Date.ToText([Date],"MMM")),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom4",{"Date", "Index", "Year", "Month Number", "Month Name", "Day", "Day Name"}),
#"Added Custom5" = Table.AddColumn(#"Reordered Columns", "Quarter Number", each Date.QuarterOfYear([Date])),
#"Duplicated Column" = Table.DuplicateColumn(#"Added Custom5", "Year", "Copy of Year"),
#"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"Copy of Year", "Short Year"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Short Year", type text}}),
#"Split Column by Position" = Table.SplitColumn(#"Changed Type","Short Year",Splitter.SplitTextByRepeatedLengths(2),{"Short Year.1", "Short Year.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Short Year.1", Int64.Type}, {"Short Year.2", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Short Year.1"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Short Year.2", "Short Year"}}),
#"Added Custom6" = Table.AddColumn(#"Renamed Columns2", "Quarter Year", each Number.ToText([Short Year]) & "Q" & Number.ToText([Quarter Number],"00")),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Custom6",{"Index", "Date", "Day", "Day Name", "Month Number", "Month Name", "Quarter Number", "Quarter Year", "Short Year", "Year"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns1",{{"Date", type date}, {"Day", Int64.Type}, {"Index", Int64.Type}, {"Month Number", Int64.Type}, {"Quarter Number", Int64.Type}, {"Month Name", type text}, {"Quarter Year", type text}, {"Year", Int64.Type}}),
 #"Added Conditional Column" = Table.AddColumn(#"Changed Type2", "Fiscal Year", each if [Month Number] < #"Fiscal Start Month" then [Year] else [Year]+1 ),
 #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Fiscal Month", each if [Month Number] < #"Fiscal Start Month" then [Month Name] else [Month Name] ),
 #"Added Custom7" = Table.AddColumn(#"Added Conditional Column1", "Fiscal Month Sort Order", each Number.Mod(Date.Month([Date])+FiscalMonthCalc ,12)+1),
 #"Added Conditional Column3" = Table.AddColumn(#"Added Custom7", "Fiscal Quarter", each if [Fiscal Month Sort Order] >= 1 and [Fiscal Month Sort Order] <= 3 then "Q1"
 else if [Fiscal Month Sort Order] >= 4 and [Fiscal Month Sort Order] <= 6 then "Q2"
 else if [Fiscal Month Sort Order] >= 7 and [Fiscal Month Sort Order] <= 9 then "Q3"
 else if [Fiscal Month Sort Order] >= 10 and [Fiscal Month Sort Order] <= 12 then "Q4"
 else "Q Unknown" ),
#"Added Conditional Column4" = Table.AddColumn(#"Added Conditional Column3", "Fiscal Quarter Sort Number", each if [Fiscal Month Sort Order] >= 1 and [Fiscal Month Sort Order] <= 3 then "1"
 else if [Fiscal Month Sort Order] >= 4 and [Fiscal Month Sort Order] <= 6 then "2"
 else if [Fiscal Month Sort Order] >= 7 and [Fiscal Month Sort Order] <= 9 then "3"
 else if [Fiscal Month Sort Order] >= 10 and [Fiscal Month Sort Order] <= 12 then "4"
 else "Q Unknown" ),
 #"Changed Type3" = Table.TransformColumnTypes(#"Added Conditional Column4",{{"Fiscal Quarter Sort Number", Int64.Type}, {"Fiscal Month Sort Order", Int64.Type}, {"Fiscal Year", Int64.Type}}),
 #"Renamed Columns3" = Table.RenameColumns(#"Changed Type3",{{"Date", "Calendar Date"}, {"Month Number", "Calendar Month Number"}, {"Month Name", "Calendar Month Name"}, {"Quarter Number", "Calendar Quarter Number"}, {"Quarter Year", "Calendar Quarter Year"}, {"Short Year", "Calendar Short Year"}, {"Year", "Calendar Year"}})

in
 #"Renamed Columns3"
in
    Date

Ashish_Mathur · ‎07-21-2023

Hi,

Please find attached the PBI file.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

MCosta_BI · ‎07-21-2023

I am very thankful for your work, time and generosity. Thank you very much. You are a great human being. It works great!

Ashish_Mathur · ‎07-21-2023

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Ashish_Mathur · ‎07-18-2023

Hi,

Please find attached my solution file.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Rolling 12 months (From Earliest_Use_date) + sum of value

Helpful resources

Europe’s largest Microsoft Fabric Community Conference

New forum boards available in Real-Time Intelligence.

Power BI Monthly Update - May 2024

How to Get Your Question Answered Quickly

Jumpstart your career with the Fabric Career Hub

Rolling 12 months (From Earliest_Use_date) + sum of value

Helpful resources

Europe’s largest Microsoft Fabric Community Conference

New forum boards available in Real-Time Intelligence.

Power BI Monthly Update - May 2024

How to Get Your Question Answered Quickly