Dax Measure to calculate Billing of active deals

[Laila92]

I have deals that have different types of stages:
Live Stage

Cancellation Stage

Lost Stage

Onboarding stage.

My data has a date for the start of each of these stages. Not every deal has a cancellation date, some go straight to lost.
We bill our customers only when they are in Live stage. I am trying to write a measure that shows the Live deals over time, and the money we are billing for that.  Example data:

Go-Live Date	Lost time	ID	Cancellation Request Start Date (Hide)	Onboarding Start Date	Monthly Price
2020-02-06 0:00:00	2020-03-03 0:00:00	17818		2020-02-04 0:00:00	$100.00
2020-02-03 0:00:00	2020-02-10 0:00:00	17468	2020-02-11 0:00:00	2020-01-31 0:00:00	$100.00
2020-02-11 0:00:00	2020-02-03 0:00:00	17469	2020-02-11 0:00:00	2020-01-31 0:00:00	$100.00
2020-01-07 0:00:00	2020-03-26 0:00:00	17150		2020-01-07 0:00:00	$400.00
2020-01-06 0:00:00	2020-03-31 0:00:00	17215	2020-03-16 0:00:00	2020-01-02 0:00:00	$849.00
2020-01-08 0:00:00	2020-03-31 0:00:00	16807	2020-02-18 0:00:00	2020-01-02 0:00:00	$150.00

So for example, for deal ID 16807, I want the Measure to show 150$ from Jan 2020 to March 2020 (450$ in total). I have been trying to implement this measure discussed by @Greg_Deckler in https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365, but can't get it to work for my data.

Total Amount = 
VAR tmpCalendar = ADDCOLUMNS('Calendar',"Month",MONTH([Date]),"Year",YEAR([Date]),"MonthYear",VALUE(YEAR([Date]) & FORMAT(MONTH([Date]),"0#")))
VAR tmpBilling = ADDCOLUMNS('Billing',"MonthYearBegin",VALUE(YEAR([BeginDate]) & FORMAT(MONTH([BeginDate]),"0#")),
                                      "MonthYearEnd",VALUE(YEAR([UntilDate]) & FORMAT(MONTH([UntilDate]),"0#")))
VAR tmpTable = 
SELECTCOLUMNS(
    FILTER(
        GENERATE(
            tmpBilling,
            SUMMARIZE(tmpCalendar,[Year],[Month],[MonthYear])
        ),
        [MonthYear] >= [MonthYearBegin] &&
        [MonthYear] <= [MonthYearEnd]
    ),
    "Customer",[Customer],
    "Year",[Year],
    "Month",[Month],
    "Amount",[Amount]
)
RETURN SUMX(tmpTable,[Amount])

[Greg_Deckler · Accepted solution]

@Laila92 - Since I wrote that, perhaps I can help. See attached below signature line. Let me know if you need further assistance!

[Greg_Deckler · Accepted solution]

@Laila92 - Since I wrote that, perhaps I can help. See attached below signature line. Let me know if you need further assistance!

[AndreaMeras]

@Greg_Deckler I need your help!

I used your formula to calculate but I must have something wrong becasue the result is exactly the double of what Im suppossed to have

My scenary is the following. I have contracts with a daily ammount and I need the cumulative by month (fiscal month). So, first I generated the fiscal revenue (by multiplying the daily ammount by the number of fiscal days inside the month). This is done with a formula.
Then, I used your formula to have the cumulative only in the period of vigency.

But....I must be doing something wrong becasue the results in each row is the double of the real monthy revenue.

Any idea?

[Greg_Deckler]

@AndreaMeras Can you post sample data and expected output?

[AndreaMeras]

Here is an example. Taxa is the daily value, Fiscal days are the number of days in the month, REv Fiscal is the month value and Prod y using your formula, but as yoy can see, it gives me double

[AndreaMeras]

@Greg_Deckler  I think the proble is that I need a formula for a daily ammount instead of monthly. How can I edit that? thanks!!

[Laila92]

Thank you Greg! I am getting the Error "Cannot convert value "" of type Text to type Number." I guess this could be because I have blank values in my data, but I am not sure where in the measure to add var<>blank(). 

[v-lili6-msft]

hi  @Laila92 

Yes, it is that there are some blank value in [Go-Live Date] column or [Lost time] column.

So you just need to add a filter as below:

Total Amount =

VAR tmpCalendar = ADDCOLUMNS('Calendar',"Month",MONTH([Date]),"Year",YEAR([Date]),"MonthYear",VALUE(YEAR([Date]) & FORMAT(MONTH([Date]),"0#")))

VAR tmpBilling = ADDCOLUMNS(FILTER('Billing',Billing[Go-Live Date]<>BLANK()&&Billing[Lost time]<>BLANK()),"MonthYearBegin",VALUE(YEAR([Go-Live Date]) & FORMAT(MONTH([Go-Live Date]),"0#")),

"MonthYearEnd",VALUE(YEAR([Lost time]) & FORMAT(MONTH([Lost time]),"0#")))

VAR tmpTable =

SELECTCOLUMNS(

FILTER(

GENERATE(

tmpBilling,

SUMMARIZE(tmpCalendar,[Year],[Month],[MonthYear])

),

[MonthYear] >= [MonthYearBegin] &&

[MonthYear] <= [MonthYearEnd]

),

"Customer",[ID],

"Year",[Year],

"Month",[Month],

"Amount",[Monthly Price]

)

RETURN SUMX(tmpTable,[Amount])

Regards,

Lin

[Laila92]

I made the change and it did fix the error, but I don't see any data output, when i put the measure in a table I don't see any numbers. any idea why?

[v-lili6-msft]

hi  @Laila92 

It works well on my side, please try it, if you still have the problem, please share your sample pbix file for us have a test.

Regards,

Lin

[Laila92]

I found the issue (was using a measure for the amount value that had a wrong filter on it). The measure does work now, however, for clients that do not have a lost date (that are still currently paying), i don't get any value. 
I should build in an if condition in the defintion of tmpBilling where if the lost time = blank it returns todays date.. does that make sense?

It would look something like this: 

I am just not sure how to implement.

VAR tmpBilling =  
                ADDCOLUMNS( 
                    'Deals',
                        "MonthYearBegin",VALUE(YEAR([Go-Live Date]) & FORMAT(MONTH([Go-Live Date]),"0#")),
                        IF([Lost Time] = BLANK(), "MonthYearEnd" = TODAY(),"MonthYearEnd",VALUE(YEAR([Lost time]) & FORMAT(MONTH([Lost time]),"0#"))
                         )
                        )

[v-lili6-msft · Accepted solution]

hi  @Laila92 

If so, adjust the formula as below:

Total Amount 333 = 
VAR tmpCalendar = ADDCOLUMNS('Calendar',"Month",MONTH([Date]),"Year",YEAR([Date]),"MonthYear",VALUE(YEAR([Date]) & FORMAT(MONTH([Date]),"0#")))
VAR tmpBilling = ADDCOLUMNS('Billing',"MonthYearBegin",VALUE(YEAR([Go-Live Date]) & FORMAT(MONTH([Go-Live Date]),"0#")),
                                      "MonthYearEnd",IF(ISBLANK([Lost time]),VALUE(YEAR(TODAY()) & FORMAT(MONTH(TODAY()),"0#")),VALUE(YEAR([Lost time]) & FORMAT(MONTH([Lost time]),"0#"))))
VAR tmpTable = 
SELECTCOLUMNS(
    FILTER(
        GENERATE(
            tmpBilling,
            SUMMARIZE(tmpCalendar,[Year],[Month],[MonthYear])
        ),
        [MonthYear] >= [MonthYearBegin] &&
        [MonthYear] <= [MonthYearEnd]
    ),
    "Customer",[ID],
    "Year",[Year],
    "Month",[Month],
    "Amount",[Monthly Price]
)
RETURN SUMX(tmpTable,[Amount])

VAR tmpBilling = ADDCOLUMNS('Billing',"MonthYearBegin",VALUE(YEAR([Go-Live Date]) & FORMAT(MONTH([Go-Live Date]),"0#")),

"MonthYearEnd",IF(ISBLANK([Lost time]),VALUE(YEAR(TODAY()) & FORMAT(MONTH(TODAY()),"0#")),VALUE(YEAR([Lost time]) & FORMAT(MONTH([Lost time]),"0#"))))

Regards,

Lin