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.
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
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?
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.
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.
It’s time for another PBI Community recap!
Visit our Community Blog for articles, guides, and information created by fellow community members.
Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.
Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!