Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have a data table with only YTD sales value, by month, and would like to calculate the MTD values. Being a PowerBi beginner I cannot figure out how to get this done and when searching the forum for clues, all I found was answers on how to solve the opposite problem, i.e. calculating MTD/QTD/YTD etc from daily or monthly figures.
Below is a sample table of how the data looks and fyi, I managed to create a date_table which I guess comes in handy here.
Month (YYYY-MM) | YTD Sales | MTD Sales |
2019-12 | 1000 | ? |
2020-01 | 100 | ? |
2020-02 | 300 | ? |
2020-03 | 500 | ? |
2020-04 | 900 | ? |
I appreciate any support!
Stay safe,
Erik
Solved! Go to Solution.
Try this calculated column. I recommend a calculated column instead of a measure, because having MTD in a column will allow you to perform various time intelligence calculations.
MTD Sales =
VAR vDate = YTDtoMTD[Month Ending]
VAR vPrevDate =
CALCULATE (
MAX ( YTDtoMTD[Month Ending] ),
ALL ( YTDtoMTD ),
YTDtoMTD[Month Ending] < vDate
)
VAR vPrevSales =
CALCULATE (
SUM ( YTDtoMTD[YTD Sales] ),
ALL ( YTDtoMTD ),
YTDtoMTD[Month Ending] = vPrevDate
)
VAR vYTD = YTDtoMTD[YTD Sales]
VAR vMTD = vYTD - vPrevSales
VAR vResult =
IF ( MONTH ( vDate ) = 1, vYTD, vMTD )
RETURN
vResult
Proud to be a Super User!
Try this calculated column. I recommend a calculated column instead of a measure, because having MTD in a column will allow you to perform various time intelligence calculations.
MTD Sales =
VAR vDate = YTDtoMTD[Month Ending]
VAR vPrevDate =
CALCULATE (
MAX ( YTDtoMTD[Month Ending] ),
ALL ( YTDtoMTD ),
YTDtoMTD[Month Ending] < vDate
)
VAR vPrevSales =
CALCULATE (
SUM ( YTDtoMTD[YTD Sales] ),
ALL ( YTDtoMTD ),
YTDtoMTD[Month Ending] = vPrevDate
)
VAR vYTD = YTDtoMTD[YTD Sales]
VAR vMTD = vYTD - vPrevSales
VAR vResult =
IF ( MONTH ( vDate ) = 1, vYTD, vMTD )
RETURN
vResult
Proud to be a Super User!