DAX YTD via PowerPivot

[dluhut]

Hello,

I am using Excel 2013 PowerPivot. Thus would appreciate if you could help me out using DAX that's in PowerPivot Excel 2013.

What I am trying to accomplished here is to have the YTD.

I am sorry I can't upload a sample due to confidentiality, but I have tried my very best in giving the necessary information. If it's not enough, please let me know and I can upload more.

As you can see in the attached pictures, I have a fact table, 'FIN Trial Balance', but due to different fiscal period end, some being in December, some being in April, and some being in August, thus, in order to calculate the YTD and to be in the same 'period', I have thus created a 'Fiscal Calendar Date'.

I have tried https://community.powerbi.com/t5/Desktop/DAX-Running-Total-YTD/m-p/21576/highlight/true#M6692  and  http://www.daxpatterns.com/cumulative-total/ but my YTD is still the same as MTD.

May I know what did I do wrong here?

FYI, my 'Calendar Date' and 'Fiscal Calendar Date' are in 1/1/20xx (Jan), 2/1/20xx (Feb), ... ... , 11/1/20xx (Nov) and 12/1/20xx (Dec). They are in months and not day.

Also, the 'month' slicer is from 'Calendar DateTable'[Month], so that user's thought of calendar reflect calendar date.

[dluhut]

Any experts here able to help me out?

I believe my problem is that, the slicer 'Month' is taken from the 'Calendar Date Table'.

However, my DAX formula here is not taking into consideration the 'link' between 'Calendar Date Table' and 'Fiscal Calendar DateTable'.

Thus, is there a way for the YTD to know taking the beginning to the 'end' of the month of the 'Fiscal Calendar DateTable' via the 'Month' slicer of 'Calendar Date Table'?

[Ashish_Mathur]

Hi,

Upload a summy dataseta and how your expected result

[v-shex-msft]

HI @dluhut.

For these refer links which you mention, they use the calculated column to get the result. Please make sure if you are use these formula in calculated column.

BTW, if your requirement is about power pivot, you can post it at below forum.

Msdn forums - Power Pivot - Microsoft

Regards,

Xiaoxin Sheng