Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Matrix not aggregating correctly (month/year)

This matrix visual worked fine for years. Its source was a SharePoint site that had full date and time, but after pulling it in to Power BI I formatted the date by Month/Year only (we only care about reporting out on a monthly basis). The data is supposed to look like this:

 

 

There was a recent corporate change where after Date X (actually August, 2021) the data came from a different SharePoint site. Since we still need access to historical data, instead of hard coding a change to the new site I moved the data from the two different SharePoint sites to secondary columns, both formatted as Date: Month/Year. Then the original column that was the  direct import from SharePoint site A became instead an IF statement, where "Before Date X" it's Dataset A, and "After Date X" it's Dataset B. That column (the IF statement) is also formatted as Date: Month/Year.

That was the only change, and yet now it seems to aggregating by Day/Month/Year, even though no part of any column includes Day in the format. What am I missing?

 

 

2 Replies

  • Anonymous,

     

    Try using the FORMAT function in a calculated column:

     

    Month/Year = FORMAT ( DimDate[Date], "mmm, yyyy" )
  • Anonymous's avatar
    Anonymous
    Not applicable

    Sorry, I've resolved this on my own. I would delete the question but I don't see a way to.