Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Date Filtering on a Measure Not Working

I have one table that has 'snapshot date', 'report name', and 'automated'. I'm trying to retreive those from the most recent month that are automated. Automated is a binary column that reports 1 if the report is automated. Here are the sample PBIX where I'm working on the issue and the data.

When I try the following, it does not sum for the most recent month, but for the whole dataset. 

current month sum of automated reports =
CALCULATE (
    SUM ( 'Automated Report Record'[Automated] ),
    FILTER (
        'Automated Report Record',
        AND (
            MONTH ( 'Automated Report Record'[Snapshot Date] ) = [Latest Month],
            YEAR ( 'Automated Report Record'[Snapshot Date] ) = [Latest Year]
        )
    )
)

 

When I hard code the measures of Latest Month and Latest Year, it works as expected returning 6, but with the measures as the value, it doesn't work. 

Latest Month and Latest Year defined as the following:

Latest Month = MONTH(LASTDATE('Automated Report Record'[Snapshot Date])) 

Latest Year = YEAR(LASTDATE('Automated Report Record'[Snapshot Date]))


I've found the following post, but the solutions haven't solved my problem. 

 

Please help me understand what I'm missing here.

Thanks,

Chris

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable

      Could you please help me understand what the issue was in my code block as well? 


      Best, 


      Chris