Calculate MTD based on YTD figures

[Anonymous]

Hello,

I have a monthly report that is providing figures on a YTD basis. From here, I would like to calculate MTD figures, but it’s not that simple.

The challenge I have is the following, for example, account 8514004000 in January has different cost centers than in February, etc. The YTD (column “Actual”) in January for this account is equal to 4,477 and for February is equal to 9,168. Therefore, the MTD for January should remain the same (4,477), but MTD for February should equal 4,691.

Therefore, my calculation (calculated column, DAX below) doesn’t really make sense and is not accurate:

Month To Date =
VAR _sourceName = ZFR001_Appended[Source.Name]
VAR _glAcc = ZFR001_Appended[GL Account]
VAR _ccpc = ZFR001_Appended[CCPC]
VAR _status = ZFR001_Appended[Status]
RETURN
if(month(ZFR001_Appended[Date])=1,ZFR001_Appended[Actual],
ZFR001_Appended[Actual]-
calculate(
sumx(ZFR001_Appended, ZFR001_Appended[Actual]),
filter(
all(ZFR001_Appended),
ZFR001_Appended[GL Account]=_glAcc &&
ZFR001_Appended[CCPC]=_ccpc &&
ZFR001_Appended[Status]=_status
),
dateadd(ZFR001_Appended[Date],-1,MONTH)))

My *pbix is attached for reference.

https://marekre-my.sharepoint.com/:u:/g/personal/marekre_marekre_onmicrosoft_com/EQHAmXvwbb5BhCKeQhv...

Thank you in advance for help,

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous  ,

Here are the steps you can follow：

1. Create calculated column.

YTD =
CALCULATE(SUM('ZFR001_Appended'[Actual (YTD)]),FILTER('ZFR001_Appended','ZFR001_Appended'[GL Account]=EARLIER('ZFR001_Appended'[GL Account])&&'ZFR001_Appended'[Source.Name]=EARLIER('ZFR001_Appended'[Source.Name])))

MTD =
var _lastmonth=
CALCULATE(SUM('ZFR001_Appended'[Actual (YTD)]),FILTER(ALL('ZFR001_Appended'),'ZFR001_Appended'[GL Account]=EARLIER('ZFR001_Appended'[GL Account])&&[Source.Name]=EARLIER([Source.Name])-1))
var _thismonth=
CALCULATE(SUM('ZFR001_Appended'[Actual (YTD)]),FILTER(ALL('ZFR001_Appended'),'ZFR001_Appended'[GL Account]=EARLIER('ZFR001_Appended'[GL Account])&&[Source.Name]=EARLIER([Source.Name])))
return
_thismonth-_lastmonth

2. Select [GL Account] is 8514004000 in Filter, select [Source.Name] is 202001 or 202002

3. Result.

Best Regards,

Liu Yang

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

[amitchandak]

@Anonymous . I checked the data. I doubt you can have the correct column. With help from a date table create three measures like these

YTD = CALCULATE(SUM(ZFR001_Appended[Actual (YTD)]),DATESMTD('Date'[Date]))

last YTD = CALCULATE(SUM(ZFR001_Appended[Actual (YTD)]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

MTD = [YTD] -[last YTD]

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Anonymous]

Thank you for your help. However it doesn't work, the result of each measure is blank