Filter not working with measure results

[CourtneyLeah]

Hi,

I have a report that looks at all of our health and safety data. One of the filters that I have on this report ensures that only data associated with a date less than or equal to today is shown. This seems to work fine with the raw data and calculated columns. However, when I add a calculated measure to any visual, the filter appears to stop working.

I am relatively new to Power BI, so if anyone could explain to me why this is happening and how to go about fixing it, it would be much appreciated.

Cheers

[Phil_Seamark]

Hmm, it sounds like something that should work ok for you.

Is there anyway you can post a cut down version of your file so we can help you debug the issue?

Or at the very least, post the code for the Calculated Measure you have used.  If it uses any filtering in the measure, this could be what is causing the behaviour.

[CourtneyLeah]

This is the the measure that I'm using:

TRIFR = (calculate(sum(TRIFR[Fatal])+sum(TRIFR[Notifiable])+sum(TRIFR[Lost Time])+sum(TRIFR[Medical Tretment])+sum(TRIFR[Reduced Work]), datesinperiod(DateKey[Date], lastdate(DateKey[Date]), -12, MONTH))*1000000)/calculate(sum(TRIFR[Work Hours]), datesinperiod(DateKey[Date], lastdate(DateKey[Date]), -12, MONTH))

If you need any other info, let me know

[Phil_Seamark]

I created a sample table using your column headers and called the table TRIFR and added some dummy data in the rows.

I also created a date table called DateKey and created a relationship between the two tables.

I added your measure to my TRIFR table and it produces a value which makes sence for my dummy data.

Do you have a relationship between the two tables?

And why are you using the LASTDATE() fuction in your formula?  This will return the very last date in your date table, so if your date table stretches out way in the future, you'll only be calcuating over the 12 months of the end of your date table.

What is the name of your date column in TRIFR?

[CourtneyLeah]

1. Yes, each item in the TRIFR table has a date associated to it (Incident Date)

2. As I said before, I'm kind of new to Power BI. I've googled my way to the knowledge that I do have, and LASTDATE() has worked for me in similar situations previously. However, if there is a better way to do it, I'd love to know.

[Sean]

@CourtneyLeah

"One of the filters ??? that I have on this report ensures that only data associated with a date less than or equal to today is shown. This seems to work fine with the raw data and calculated columns. However, when I add a calculated measure to any visual, the filter appears to stop working."

I'm not exactly sure what you mean when you say the filter stops working? What filter are you referring to?

Also try referencing the [Incident Date] in your DATESINPERIOD function

basically change all DateKey[Date] to TRIFR[Incident Date]

TRIFR =
 (
    CALCULATE (
        SUM ( TRIFR[Fatal] ) + SUM ( TRIFR[Notifiable] )
            + SUM ( TRIFR[Lost Time] )
            + SUM ( TRIFR[Medical Tretment] )
            + SUM ( TRIFR[Reduced Work] ),
        DATESINPERIOD ( TRIFR[Incident Date], LASTDATE ( TRIFR[Incident Date] ), -12, MONTH )
    )
        * 1000000
)
    / CALCULATE (
        SUM ( TRIFR[Work Hours] ),
        DATESINPERIOD ( TRIFR[Incident Date], LASTDATE ( TRIFR[Incident Date] ), -12, MONTH )
    )

 EDIT: Well I think there's your answer

do you have data all the way to June 30, 2020? are you getting blank

you are filtering the table to include only Jun 30, 2019 to Jun 30, 2020

[Phil_Seamark]

How to you create your Date table and what is the last date in that table?

[CourtneyLeah]

Created using CALENDAR() and the last date in the table is 30/06/2020

[Phil_Seamark · Accepted solution]

Yeah, I think that might be the issue.

Athough this still probably only gives you the latest 12 months.  There are possibly some better techniques to achieve the same thing.  Let us know how you get on.

TRIFR
    = (
        CALCULATE (
            SUM ( TRIFR[Fatal] ) + SUM ( TRIFR[Notifiable] )
                + SUM ( TRIFR[Lost Time] )
                + SUM ( TRIFR[Medical Tretment] )
                + SUM ( TRIFR[Reduced Work] ),
            DATESINPERIOD ( DateKey[Date], LASTDATE ( 'TRIFR'[Incident Date]  ), -12, MONTH )
        )
            * 1000000
    )
        / CALCULATE (
            SUM ( TRIFR[Work Hours] ),
            DATESINPERIOD ( DateKey[Date], LASTDATE ('TRIFR'[Incident Date]  ), -12, MONTH )
        )

[Sean]

@Phil_Seamark 

DATESINPERIOD would work even without a Calendar table!

(which is why I used the same [Incident Date] column in my formula above for both the dates and the start_date )

in case [Incident Date] was not the column used to create the relationship but some other [Date Column]...

unlike DATEADD which requires you to reference a column with contiguous dates

[Phil_Seamark]

I'm guessing the DATESINPERIOD would be slightly quicker not needing to perform I/O on a Calendar table.