cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Laila92 Helper III
Helper III

Dax Measure to calculate Billing of active deals

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

 

 

 

 




 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User IV
Super User IV

Re: Dax Measure to calculate Billing of active deals

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


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Community Support
Community Support

Re: Dax Measure to calculate Billing of active deals

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Super User IV
Super User IV

Re: Dax Measure to calculate Billing of active deals

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


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Laila92 Helper III
Helper III

Re: Dax Measure to calculate Billing of active deals

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(). 

Community Support
Community Support

Re: Dax Measure to calculate Billing of active deals

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
Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Laila92 Helper III
Helper III

Re: Dax Measure to calculate Billing of active deals

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?

Community Support
Community Support

Re: Dax Measure to calculate Billing of active deals

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Laila92 Helper III
Helper III

Re: Dax Measure to calculate Billing of active deals

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#"))
                         )
                        )
Community Support
Community Support

Re: Dax Measure to calculate Billing of active deals

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors