Value of last month in DAX measure

[Laila92]

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

[v-lid-msft · Accepted 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,