Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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])
Solved! Go to Solution.
@Laila92 - Since I wrote that, perhaps I can help. See attached below signature line. Let me know if you need further assistance!
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])
Regards,
Lin
@Laila92 - Since I wrote that, perhaps I can help. See attached below signature line. Let me know if you need further assistance!
@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?
@AndreaMeras Can you post sample data and expected output?
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
@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!!
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().
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:
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?
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
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#"))
)
)
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])
Regards,
Lin
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |