DAX formula to count a date field, filtered by current and previous years

[Anonymous]

Hi all,

I am trying to create a measure to count the number of reports sent, and filter it by current year, previous year, 2 years ago, etc.

Table I'm using: V_SUBMISSIONFORM_BI

Field I'm using: REPORT_SENT_DATE (it's a date field) 

I've made a few DAX commands with correct syntax, but can't figure out how to filter by year:

ReportsSentCurrentYear = CALCULATE(COUNT('V_SUBMISSIONFORM_BI'[REPORT_SENT_DATE]), FILTER ('V_SUBMISSIONFORM_BI', YEAR(TODAY()))

ReportsSentPreviousYear = CALCULATE(COUNT('V_SUBMISSIONFORM_BI'[REPORT_SENT_DATE]), FILTER ('V_SUBMISSIONFORM_BI', YEAR(TODAY())-1)

My ultimate goal is to display a score if the count of reports in the current year is greater than the count of reports in the previous year. It's like measuring changes in sales overtime, but instead its changes in counts overtime. What I got on this front:

GaugeReportsSent = IF(CALCULATE(COUNT('V_SUBMISSIONFORM_BI'[REPORT_SENT_DATE]), FILTER ('V_SUBMISSIONFORM_BI', YEAR(TODAY()))> CALCULATE(COUNT('V_SUBMISSIONFORM_BI'[REPORT_SENT_DATE]), FILTER ('V_SUBMISSIONFORM_BI', YEAR(TODAY())-1),1,0)

Any help correcting these commands is deeply appreicated. Thank you.

[az38]

hi @Anonymous 

you have incorrect filter statement. try smth like

CALCULATE(COUNT('V_SUBMISSIONFORM_BI'[REPORT_SENT_DATE]), 
FILTER (ALL('V_SUBMISSIONFORM_BI'), YEAR(SELECTEDVALUE([REPORT_SENT_DATE]))=YEAR(TODAY()))

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

Hi az38,

Thanks for your help. I fixed my filter statement and the syntax works, but the result is "(Blank)" when I add the measure to a card or other visualziation,  when I know there should be 4 reports for the current year/2020. I tried switching out the field with other date fields I'd like to count as well, but all say "(Blank)" too. What I currently have:

ReportsCurrentYear = CALCULATE(COUNT('V_SUBMISSIONFORM_BI' [REPORT_SENT_DATE], FILTER (ALL('V_SUBMISSIONFORM_BI'), YEAR(SELECTEDVALUE('V_SUBMISSIONFORM_BI' [REPORT_SENT_DATE]))=YEAR(TODAY())) 

Any additional assistance getting this to work is greatly appreciated.