Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Help changing months

I have been using the below formula to calculate my fired employees but it is offsetting it by a month. Can anyone help me shift it properly so that it display on the month they were fired rather than the month after? 

(August should show 8 and September should show 3)

 
 
 

Fired = 
VAR currDate =
    MAX ( Sheet1[Date] )
VAR currList =
    CALCULATETABLE (
        VALUES ( Sheet1[EmployeeID] ),
        FILTER (
            ALLSELECTED ( Sheet1 ),
            [Date] >= DATE ( YEAR ( currDate ), MONTH ( currDate ), 1 )
                && [Date]
                    <= DATE ( YEAR ( currDate ), MONTH ( currDate ) + 1, 1 )
                        - 1
        )
    )
VAR prevList =
    CALCULATETABLE (
        VALUES ( Sheet1[EmployeeID] ),
        FILTER (
            ALLSELECTED ( Sheet1 ),
            [Date]
                >= DATE ( YEAR ( currDate ), MONTH ( currDate ) - 1, 1 )
                && [Date]
                    <= DATE ( YEAR ( currDate ), MONTH ( currDate ), 1 ) - 1
        )
    )
RETURN
    COUNTROWS ( EXCEPT ( prevList, currList ) )
        + IF ( DISTINCTCOUNT ( Sheet1[EmployeeID] ) <> 0, 0, BLANK () ) 

 

2 Replies

  • If possible please share a sample pbix file after removing sensitive information.
    Thanks

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support

    Hi Anonymous ,

     

    Could you please share your sample data and excepted result to me if you don't have any confidential Information. Please upload your files to One Drive and share the link here.