TOTALYTD measure works, but TOTALMTD on same fields returns no value

[rar4010]

Hi All -

I'm having a problem with TOTALMTD not returning a value...here is the scenario:

I'm using financial data and trying to compare MTD and YTD actual to budget. Since I only want to display months that contain actual data, I am using this measure to eliminate the months that do not contain actual amounts yet..

Sum Amount = SUMX(FILTER(Fact_Amounts,Fact_Amounts[DateKey]<=Fact_Amounts[MaxDate]),Fact_Amounts[Amount])

where MaxDate is derived from the MAX of all the date keys for actual amounts. I then use TOTALMTD and TOTALYTD to calculate actual, budget and variance.

Where I have a problem is MTD amounts will not display unless I specifically select a month, but YTD works fine. The odd thing is that if I select the prior year (which has full 12 months of data) in my slicer, the MTD totals appear and display December of the prior year.

[rar4010]

Not sure how to post the data, but here are some screenshots. My MTD Actual measure is as follows.

MTDAct Amount = TOTALMTD('Fact_Amounts'[Sum Amount],'Dim_Date'[CalendarDate],Dim_Scenario[ScenarioID]=1)

When 2015 is selected in the slicer, I get this...

Change the slicer to 2016 and MTD disappears.

Select a month in upper left visual and MTD Actual reappears.

It appears that MTD has to have an explicit month selected to work, but why would it work for 2015 without specifying a month?

Yes there is a Date file with relationship to the Fact_Amounts table.

Does this help any?

[v-yuezhe-msft]

Hi @rar4010,

TOTALMTD (Expression, Date_Column [, SetFilter]) works in a date dimension, which must have contiguous, nonrepeating dates from January 1 of the first year you have data to December 31 of the last year you have data. Your formula that create MTDAct Amount measure looks good for me. To analyze this issue, we need to know the data of your original tables.

Could you please copy data of your original tables to Excel and share the Excel file here? You can upload the Excel file in OneDrive and post the shared link here.


Thanks,
Lydia Zhang

[rar4010]

Hi Lydia,

Thanks for your response, but my company has not allowed access to OneDrive due to security reasons so I cannot upload my data there. Is there another option via the forum to upload files?

Thanks,

Andy

[v-yuezhe-msft]

Hi @rar4010，

You can add PBIX file using Attachments option in  the following screenshot.


Thanks,
Lydia Zhang

[CheenuSing]

Hi Lydia Zhang,

I do not see the attachment at the bottom of the Body as shown by you. I am using WIndows 10 and chrome. Any specific browser to be used to enable the attachment box.  Or let us know how to enable it.

Cheers

CheenuSing

[rar4010]

I was about to reply the same @CheenuSing

I was using Windows 10 and IE11 and did not have the Attachments option. I just tried with Chrome and still do not have Attachments

[v-yuezhe-msft]

Hi @rar4010,

I found that only community members who have advanced permission can use the Attachments option to upload files. Is there any possibility that you upload files to other online tool?

Thanks,
Lydia Zhang

[CheenuSing]

Hi @rar4010

Can you post some data and output you expect. Do you have a calendar Table.

Cheers

CheenuSing