Combining multiple columns into one measure - Time Intelligence

[Stu_clayton]

Hello all,

I am new to these forums but have been using Power BI for a number of years.

I have a complex data set where I am trying to combine multiple MTD Balance Columns into one measure that will adjust according to selections made with a slicer. Let me first explain the data set.

I have a date column and 12 MTD Balance columns. My data looks something like this (simplified):

Date	MTD1	MTD2	MTD3
3/30/2020	100	105	111
3/31/2020	100	105	117
4/1/2020	100	105	117
4/2/2020	100	105	124
4/3/2020	100	105	124

Essentially, what the data is saying is on 3/30 the MTD balance in January was 100, February 105, March 117, etc.

What I am trying to do is create one MTD measure that will pull in the correct MTD column based off of what month is selected in a slicer. For example, if January is selected the MTD column that it would pull in is MTD1, February would pull MTD2, etc. I will also have a year slicer, but that is not as much of a concern right now. This has yielded the following DAX expression (shortened for brevity):

MTD = IF(CALCULATE(SUM('GL Measures'[MTD1]), FILTER('Date', 'Date'[Month_] = 1)),
CALCULATE(LASTNONBLANKVALUE('Date'[Business Date], SUM('GL Measures'[MTD1]))),

IF(CALCULATE(SUM('GL Measures'[MTD2]), FILTER('Date', 'Date'[Month_] = 2)),
CALCULATE(LASTNONBLANKVALUE('Date'[Business Date], SUM('GL Measures'[MTD2]))),

IF(CALCULATE(SUM('GL Measures'[MTD3]), FILTER('Date', 'Date'[Month_] = 3)),
CALCULATE(LASTNONBLANKVALUE('Date'[Business Date], SUM('GL Measures'[MTD3] ))))))

This works fine except for in 1 situation. If you will notice the balance of MTD3 changes between 4/1/2020 and 4/2/2020 from 117 to 124. This occurs fairly often with this data set due to adjustments being made for a couple of days following EOM. What this measure is doing is instead of pulling in 124 when March is selected, it is pulling in 117. What I need it to do is grab the most recent MTD3 value, occurring on 4/2/2020 and beyond.

Any suggestions or guidance would be greatly welcome.

Thank you

[lbendlin]

Please provide sanitized sample data in usable format (not as a picture - inserting it into a table would be good) and show the expected outcome.

[Stu_clayton]

Of course.

Sanitized sample (Data is sanitized and simplified - Weekends are excluded as it will be in my data):

Date	MTD1	MTD2	MTD3	MTD4
4/26/2021	100	105	114	180
4/27/2021	100	105	114	182
4/28/2021	100	105	114	189
4/29/2021	100	105	114	199
4/30/2021	100	105	114	201
5/3/2021	100	105	114	201
5/4/2021	100	105	114	201
5/5/2021	100	105	114	223
5/6/2021	100	105	114	223

Expected result (for any date after 5/4/2021):

A single MTD measure where when a slicer selects January, 100 is pulled in (the most recent MTD1 value), February - 105, March - 114, and April - 223.

If you need additional or more detailed information, please let me know.

Thank you.