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

YTD measure returns MTD numbers

Hello.

 

I am using a standard measure for YTD opex

YTD_Opex_Enterprise = TOTALYTD(sum(Finance_Entry[Balance_In_Enterprise]);Finance_Date[THE_DATE];Finance_Entry[Account_Grouping_3_No(lookup)]=601)

 

If I ad the measure to a table with "Date - YTD Opex - OPEX" columns the measure works as expected with YTD accumulating over the year.

 

However: If I add Month to columns the YTD measure suddenly returns a MTD calculation where at the beginning of the next month the measure returns the first number of the following months. If I remove the date colums the YTD and the opex measure returns the same value each month.

 

I really dont understand why the measure does this. Any tips or advice would be welcome.

 

YTD w date.JPGYTD w month (=MTD).JPG

YTD w only month.JPG

6 REPLIES 6
kcantor Community Champion
Community Champion

Re: YTD measure returns MTD numbers

@joleift

Are you using a seperate date table or pulling the date from a fact table?



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Highlighted
joleift
Frequent Visitor

Re: YTD measure returns MTD numbers

Hi @kcantor

 

The dates are from another table (Finance_Date), finance numbers are from Finance_Entry. I tried to ad the dates to columns in Finance_Entry from a LOOKUPVALUE against Finance_Date, but the YTD formula still returns the same errors

 

Finance relationships.JPG

kcantor Community Champion
Community Champion

Re: YTD measure returns MTD numbers

@joleift

Can you add a snip of your full data model? I see four connections. Are they all for the same two tables? You may need to specify which relationship to use if they are. Is your date table just dates with no missing dates? Is other information living in your date table other than dates?

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




joleift
Frequent Visitor

Re: YTD measure returns MTD numbers

Im adding here a snip of part of the data model. Both "Finance_Entry" and "Finance_Date" are feeds from our accounting software.

 

"Finance_Date": There will be some dates missing in here.

 

 

Relationships.JPG

Microsoft
Microsoft

Re: YTD measure returns MTD numbers

Hi @joleift,

 

Where is the Month of the table visual from? It should be from table Finance_Date. You have defined it in the formula YTD_Opex_Enterprise. Please give it a try.

 

 

Best Regards!

Dale

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

Re: YTD measure returns MTD numbers

Thank everyone for your help.  I feel that it was more of an ignorance issue with me.  I hope to help others like you have helped me.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors