Show rows only from this month

[jlarques]

Dear community,

I have a table with sales data and I want to show it in a matrix the information of the current month, including days. For instance, now is July 2021 and I want to show a data matrix only with records of July 2021, not with all records since 1st January 2021. 

I was using this formula:

Sales by month=

CALCULATE(

    SUM(Sales[Total]);

    CALENDARIO[MONTH]=MONTH(TODAY()

)

but shows me all records since January 2021 with no data and I want only records from 1st July until today.

Any ideas to do that?

Thanks for all your support and help.

Kind regards,

[Greg_Deckler · Accepted solution]

@jlarques Hard to know for sure without sample data. Your formula seems overall to be correct although CALCULATE could be causing you some problems due to how it behaves. Are you missing a relationship between Sales and Calendario? Maybe:

Sales by month = 
  VAR __Table = FILTER('Sales',MONTH('Sales'[Date]) = MONTH(TODAY()))
RETURN
  SUMX(__Table,[Total])

[Greg_Deckler · Accepted solution]

@jlarques Hard to know for sure without sample data. Your formula seems overall to be correct although CALCULATE could be causing you some problems due to how it behaves. Are you missing a relationship between Sales and Calendario? Maybe:

Sales by month = 
  VAR __Table = FILTER('Sales',MONTH('Sales'[Date]) = MONTH(TODAY()))
RETURN
  SUMX(__Table,[Total])

[jlarques]

Hi @Greg_Deckler,

thanks for your quick response.

The relationship between sales and the calendar is right and exists. 

The measure works. Thank you so much for your quick help!

I understand that I have to show the percentage between this month and the same month of last year, I'll have to calculate in the same way, to avoid that percentage can show me all records from January. Is this true?  

[Greg_Deckler]

@jlarques Yes, it would be similar, logic for last year. I'm not sure I understand the last part about percentages and January.

[jlarques · Accepted solution]

Hi @Greg_Deckler,

don't worry about that. 

I have modified other formulas to calculate last year and percentage following your formula and the calculations are correct.

Thanks again for your help, @Greg_Deckler!