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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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