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.
This is a continuation on this post: https://community.powerbi.com/t5/Desktop/Dax-Measure-to-calculate-Billing-of-active-deals/m-p/101388...
I am having issues with this bit of my measure (the second one in the post), the last part of the if statement is not returning:
IF(ISBLANK([Lost Date]),VALUE(YEAR(TODAY()) & FORMAT(MONTH(TODAY()),"0#")),
IF(DAY([Lost Date]) <> 1,VALUE(YEAR([Lost Date]) & FORMAT(MONTH([Lost Date]),"0#")),
VALUE(YEAR(LastMonth) & FORMAT(MONTH(LastMonth),"0#"))
))
My measure was working like this:
Live BILLING over time =
VAR tmpBillingPeriod = ADDCOLUMNS(Deals_Lookup,
"MonthYearBegin",
IF(ISBLANK([Live Date]),VALUE(YEAR(TODAY()) & FORMAT(MONTH(TODAY()),"0#")),VALUE(YEAR([Live Date]) & FORMAT(MONTH([Live Date]),"0#"))),
"MonthYearEnd",
IF(
ISBLANK([Lost Date]),VALUE(YEAR(TODAY()) & FORMAT(MONTH(TODAY()),"0#")),
VALUE(YEAR([Lost Date]) & FORMAT(MONTH([Lost Date]),"0#")))
)
)
VAR tmpTable =
SELECTCOLUMNS(
FILTER(
GENERATE(
tmpBillingPeriod,
SUMMARIZE(Dates,[Year],[MonthNum],[MonthYearNum])
),
[MonthYearNum] >= [MonthYearBegin]&&
[MonthYearNum] <= [MonthYearEnd]
),
"Customer",[ID],
"Year",[Year],
"Month",[MonthNum],
"Amount",[Total Value]
)
RETURN SUMX(tmpTable,[Amount])
However, I now need to include that IF the lost date is on the first day of the month, the deal is not considered LIVE for that month. So lets say a lost date is on april 1st 2020, I want the last monht with LIVE billing to be March 2020. If the lost date is on april 2nd, I want to include April 2020 in the billing months.
I adding another IF sentence, where if the DAY value = 1, I try to subtract one month from the lost date, but it is not working, see below. How can i fix this measure?
Live MRR over time =
VAR LastMonth = IF (
MONTH ( [Lost Date] ) = 1,
/* YES */
DATE ( YEAR ( [Lost Date] ) - 1, 12, 1 ),
/* NO */
DATE ( YEAR ( [Lost Date]), MONTH ( [Lost Date] ) - 1, 1 )
)
RETURN
VAR tmpBillingPeriod = ADDCOLUMNS(Deals_Lookup,
"MonthYearBegin",
IF(ISBLANK([Live Date]),VALUE(YEAR(TODAY()) & FORMAT(MONTH(TODAY()),"0#")),VALUE(YEAR([Live Date]) & FORMAT(MONTH([Live Date]),"0#"))),
"MonthYearEnd",
IF(ISBLANK([Lost Date]),VALUE(YEAR(TODAY()) & FORMAT(MONTH(TODAY()),"0#")),
IF(DAY([Lost Date]) <> 1,VALUE(YEAR([Lost Date]) & FORMAT(MONTH([Lost Date]),"0#")),
VALUE(YEAR(LastMonth) & FORMAT(MONTH(LastMonth),"0#"))
)))
VAR tmpTable =
SELECTCOLUMNS(
FILTER(
GENERATE(
tmpBillingPeriod,
SUMMARIZE(Dates,[Year],[MonthNum],[MonthYearNum])
),
[MonthYearNum] >= [MonthYearBegin]&&
[MonthYearNum] <= [MonthYearEnd]
),
"Customer",[ID],
"Year",[Year],
"Month",[MonthNum],
"Amount",[Total Value]
)
RETURN SUMX(tmpTable,[Amount])
Example data:
Value | ID | Lost time | Go-Live Date |
$1,299 | 18194 | 2020-04-12 0:00:00 | |
$950 | 10622 | 2020-04-10 0:00:00 | 2018-01-31 0:00:00 |
$499 | 15491 | 2020-04-07 0:00:00 | 2019-03-22 0:00:00 |
$0 | 17806 | 2020-04-06 0:00:00 | 2020-03-12 0:00:00 |
$0 | 17807 | 2020-04-06 0:00:00 | 2020-03-12 0:00:00 |
$0 | 17808 | 2020-04-06 0:00:00 | |
$375 | 17769 | 2020-04-06 0:00:00 | 2020-03-12 0:00:00 |
$899 | 15826 | 2020-04-06 0:00:00 | 2019-05-15 0:00:00 |
$899 | 17103 | 2020-04-06 0:00:00 | 2020-01-09 0:00:00 |
$899 | 17220 | 2020-04-06 0:00:00 | 2020-01-08 0:00:00 |
$199 | 15758 | 2020-04-02 0:00:00 | 2019-05-02 0:00:00 |
$399 | 17156 | 2020-04-01 0:00:00 | |
$399 | 17157 | 2020-04-01 0:00:00 | |
$995 | 17268 | 2020-04-01 0:00:00 |
Solved! Go to Solution.
Hi @Laila92 ,
We can use the following measure to meet your requirement, put the calculate of last previous month into Addcolumns function, since when define a var, the result will be constant instead different for each row in Addoculmns
Measure Live MRR over time =
VAR tmpBillingPeriod =
ADDCOLUMNS (
Deals_Lookup,
"MonthYearBegin", IF (
ISBLANK ( [Live Date] ),
VALUE ( YEAR ( TODAY () ) & FORMAT ( MONTH ( TODAY () ), "0#" ) ),
VALUE ( YEAR ( [Live Date] ) & FORMAT ( MONTH ( [Live Date] ), "0#" ) )
),
"MonthYearEnd", IF (
ISBLANK ( [Lost Date] ),
VALUE ( YEAR ( TODAY () ) & FORMAT ( MONTH ( TODAY () ), "0#" ) ),
IF (
DAY ( [Lost Date] ) <> 1,
VALUE ( YEAR ( [Lost Date] ) & FORMAT ( MONTH ( [Lost Date] ), "0#" ) ),
VALUE (
YEAR ( DATE ( YEAR ( [Lost Date] ), MONTH ( [Lost Date] ) - 1, 1 ) )
& FORMAT (
MONTH ( DATE ( YEAR ( [Lost Date] ), MONTH ( [Lost Date] ) - 1, 1 ) ),
"0#"
)
)
)
)
)
VAR tmpTable =
SELECTCOLUMNS (
FILTER (
GENERATE (
tmpBillingPeriod,
SUMMARIZE ( Dates, [Year], [MonthNum], [MonthYearNum] )
),
[MonthYearNum] >= [MonthYearBegin]
&& [MonthYearNum] <= [MonthYearEnd]
),
"Customer", [ID],
"Year", [Year],
"Month", [MonthNum],
"Amount", [Value]
)
RETURN
SUMX ( tmpTable, [Amount] )
By using your sample data, we can get the result like this,
If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that you have shared?
BTW, pbix as attached.
Best regards,
Hi @Laila92 ,
We can use the following measure to meet your requirement, put the calculate of last previous month into Addcolumns function, since when define a var, the result will be constant instead different for each row in Addoculmns
Measure Live MRR over time =
VAR tmpBillingPeriod =
ADDCOLUMNS (
Deals_Lookup,
"MonthYearBegin", IF (
ISBLANK ( [Live Date] ),
VALUE ( YEAR ( TODAY () ) & FORMAT ( MONTH ( TODAY () ), "0#" ) ),
VALUE ( YEAR ( [Live Date] ) & FORMAT ( MONTH ( [Live Date] ), "0#" ) )
),
"MonthYearEnd", IF (
ISBLANK ( [Lost Date] ),
VALUE ( YEAR ( TODAY () ) & FORMAT ( MONTH ( TODAY () ), "0#" ) ),
IF (
DAY ( [Lost Date] ) <> 1,
VALUE ( YEAR ( [Lost Date] ) & FORMAT ( MONTH ( [Lost Date] ), "0#" ) ),
VALUE (
YEAR ( DATE ( YEAR ( [Lost Date] ), MONTH ( [Lost Date] ) - 1, 1 ) )
& FORMAT (
MONTH ( DATE ( YEAR ( [Lost Date] ), MONTH ( [Lost Date] ) - 1, 1 ) ),
"0#"
)
)
)
)
)
VAR tmpTable =
SELECTCOLUMNS (
FILTER (
GENERATE (
tmpBillingPeriod,
SUMMARIZE ( Dates, [Year], [MonthNum], [MonthYearNum] )
),
[MonthYearNum] >= [MonthYearBegin]
&& [MonthYearNum] <= [MonthYearEnd]
),
"Customer", [ID],
"Year", [Year],
"Month", [MonthNum],
"Amount", [Value]
)
RETURN
SUMX ( tmpTable, [Amount] )
By using your sample data, we can get the result like this,
If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that you have shared?
BTW, pbix as attached.
Best regards,
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 |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
115 | |
105 | |
95 | |
79 | |
72 |