Forum Discussion
Combining multiple columns into one measure - Time Intelligence
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
2 Replies
- lbendlin
Super User
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.
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.
