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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.