Calculate MTD based on YTD figures

Power BI
1

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.

Data101.pbix (548.5 KB)

Thank you in advance for help,
Marek

2

Hi @marek.regulski! We noticed that your inquiry was left unsolved for quite some time now. We strive to answer ALL inquiries in the forum.

However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!