Forum Discussion

CourtneyLeah's avatar
CourtneyLeah
Frequent Visitor
9 years ago
Solved

Filter not working with measure results

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 sho...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    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 )
        )
CourtneyLeah's avatar
CourtneyLeah
Frequent Visitor
9 years ago

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

Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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's avatar
    Sean
    Icon for Community Champion rankCommunity Champion
    9 years ago

    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 :smileyhappy:

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

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