Card value not matching table value

Question

When I input a table (see below) using following DAX the MTD Current/MTD SPLY and YTD Current/YTD SPLY, totals return the value for the entire month of last year and thus the cards displays are not truly for the same period as last year (comparing a the complete month last year to the "to date" of current. However, the correct value (same period as last year) and date is correlate correctly in the table (top values).

MTD Current = TOTALMTD([TOTAL_SERV_COST], DATESMTD(Query1[SERVICE_RECV_DATE]))

MTD SPLY = CALCULATE([MTD Current],SAMEPERIODLASTYEAR(Query1[SERVICE_RECV_DATE]))

YTD Current = TOTALYTD([TOTAL_SERV_COST],DATESYTD(Query1[SERVICE_RECV_DATE]))

YTD SPLY = CALCULATE([YTD Current],SAMEPERIODLASTYEAR(Query1[SERVICE_RECV_DATE]))

Can anyone help me work this out?

greg_deckler · Answer

Anonymous&nbsp;Lots of potential issues. Use of TI functions, use of CALCULATE but your real issue is likely a measures total problem.&nbsp;This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

anonymous · Answer

Greg_Deckler&nbsp;I appreciate your quick response and assistance. Perhaps your solution is above my pay grade. The end intent was to show a graph, card, or KPI comparing the current period to the same period last year, and not display a table. I used the table to try to communicate my conundrum. I'm new to measures, so I don't know how to reference a table in a measure. Is there a way to rewrite my DAX for SPLY to resolve my issue?&nbsp;

greg_deckler · Answer

Anonymous&nbsp;I guess my point is, don't trust that your table calculation is correct. Table and Matrix visualizations are broken in terms of the totals they return. It is easy to have totals in Table and Matrix visaulizations NOT be the sum of all of the rows displayed in the table. So, I would trust your Card visualizations over your table/matrix visualizations any day of the week and twice on Sunday.

anonymous · Answer

Greg_Deckler&nbsp;I appreciate your expertise. I reviewed your suggested content and also found your videos on Youtube; unfortunately, it either didn't resolve the specific issue I am having or your strategy is just beyond my current capability. I reviewed the data directly from the Oracle database, outside of Power BI, and the table values are correct. My Current MTD/YTD tables and totals are correct, while the same period last year (MTD/YTD SPLY) table is correct but the total is accounting for the entire month (not the same partial MTD as Current MTD).&nbsp;Am I hopeless, or are there other avenues to try?&nbsp;

greg_deckler · Answer

Anonymous&nbsp;So generally I would construct partial months to be like the following:
Last year measure to date measure = 
  VAR __EndDate = MAX('Table'[Date]) // get max date in context
  VAR __BeginDate = MIN('Table'[Date]) // get min date in context
  VAR __LYEndDate = DATE(YEAR(__EndDate)-1,MONTH(__EndDate),DAY(__EndDate))
  VAR __LYBeginDate = DATE(YEAR(__BeginDate )-1,MONTH(__BeginDate ),DAY(__BeginDate ))
  VAR __Table = FITLER(ALL('Table'),[Date]&gt;=__LYEndDate &amp;&amp; [Date]&lt;=__LYEndDate)
RETURN
  SUMX(__Table,[Value])
This stuff is way easier with sample source data.

Forum Discussion

Card value not matching table value

6 Replies

Related content

Matching Column Values

Table Visual not Matching Calculated Visual

lookup value in another table which matches value in another

Formatting Value of Table

Grouping rows in a table based on a matching value