Calculate a Cumulative Sum and add it to a measure's last value (latest Date)

[divsforeal]

Hey 

I have a tricky situation. (attached example below) 

I have data coming from one measure until december 2021, but from jan 2022 the data is coming from another measure.

Using if clause i combined both measures based on date field.

But now, the end user wants to create a cumulative sum of the last value of Measure1 + First value of Measure 2 (by Date)

Please help me create a dax for this scenario.

My current measure is as follows:

Final Measure(required Output) = Calculate((Measure2), Table1[Date] <= Max[Table1[Date]]) + Measure 2

Thanks in advance.

[v-xiaotang · Accepted solution]

Hi @divsforeal 

According to the description I created the following model, you can try a measure like this

cul = 
VAR _lastDate =
    MAXX ( Fact2021, Fact2021[Date] )
VAR _lastvalue =
    MAXX ( FILTER ( 'Fact2021', Fact2021[Date] = _lastDate ), [Value] )
VAR _culvalue =
    SUMX (
        FILTER ( ALL ( Fact2022 ), Fact2022[Date] <= MIN ( 'Table'[Date] ) ),
        [Value]
    )
RETURN
    IF ( YEAR ( MIN ( 'Table'[Date] ) ) > 2021, _culvalue + _lastvalue )

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

You cannot measure a measure.  Your cumulative needs to be calculated in a new, independent measure. Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523