Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculate MTD based on YTD figures

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,

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

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

v-yangliu-msft_0-1619662078940.png

3. Result.

v-yangliu-msft_1-1619662078943.png

 

 

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.

View solution in original post

3 REPLIES 3
v-yangliu-msft
Community Support
Community Support

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

v-yangliu-msft_0-1619662078940.png

3. Result.

v-yangliu-msft_1-1619662078943.png

 

 

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
Super User
Super User

@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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.