cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rar4010
Frequent Visitor

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

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.

 

 

 

8 REPLIES 8
rar4010
Frequent Visitor

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...

PowerBI 2016-12-05_8-41-23.jpg

Change the slicer to 2016 and MTD disappears.

PowerBI 2016-12-05_8-41-36.jpg

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?

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Hi @rar4010

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

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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

 

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
CheenuSing
Community Champion
Community Champion

Hi @rar4010

 

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

 

Cheers

CheenuSing

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!