Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Card value not matching table value

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 t...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

Anonymous 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's avatar
Anonymous
Not applicable
3 years ago

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

Am I hopeless, or are there other avenues to try? 

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    Anonymous 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]>=__LYEndDate && [Date]<=__LYEndDate)
RETURN
  SUMX(__Table,[Value])

    This stuff is way easier with sample source data.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Ok, please bear with me. I created 5 individual measures (with my own table headers)

        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]>=__LYEndDate && [Date]<=__LYEndDate)

      and then an additional measure - SUMX(_Table, [value]) - [value] being the financial value I'm trying to calculate and it still didn't work. I feel like giving up on something that should be so simple.

      Shouldn't your DAX do the same thing SAMEASLASTYEAR function should do? It seems that you developed an impressive work around to correct a huge Microsoft flaw.