1

I am using this report to track new log-ins month to month, which currently is working fine, but I need to reset everything back to 0 - so that every fiscal year each log-in is considered new again.

Log In Table

ID Name Date
login1 Sam 1/12/2020 8:00:00 AM
login2 Sam 1/12/2020 9:00:00 AM
login3 Dave 1/12/2020 8:00:00 AM
login4 Dave 1/12/2020 9:00:00 AM
login5 Jack 1/12/2020 8:00:00 AM
login6 Sam 1/12/2020 9:00:00 AM
login7 James 2/12/2020 8:00:00 AM
login8 James 2/12/2020 9:00:00 AM
login9 James 2/12/2020 10:00:00 AM
login10 Sam 3/12/2020 8:00:00 AM
login11 Sam 3/12/2020 9:00:00 AM
login12 Steve 4/12/2020 8:00:00 AM

Using this data, filtered on February we would see Steve never signed in prior to that date, so that makes him 'new'. James is also new.

My closest attempt is the custom 'Count of New Individuals' Measure


VAR currentUsers = VALUES('Log-Ins'[Name])
VAR currentDate = MIN('Log-Ins'[Date])

VAR pastUsers = CALCULATETABLE(VALUES('Log-Ins'[Name]), 
    ALL('Log-Ins'[Date].[Month],'Log-Ins'[Date].[MonthNo],'Log-Ins'[Date].[Year])
    , 'Log-Ins'[Date]<currentDate)

VAR newUsers = EXCEPT(currentUsers,pastUsers)

RETURN COUNTROWS(newUsers)

I tried to add in a hard-coded and replace 'currentDate' with it but that gives me ALL log ins since that date month over month which I don't want. Basically I want people that have signed in even in 2020 to be considered new again, but only for that month and not again until the next year (or whatever date I define)

VAR fiscalYear = DATE(2021,2,01)

I'm thinking I have to still use currentDate but maybe filter it based on fiscalDate? Not sure how to go about this one.

Insan3
  • 37
  • 7
  • Is your Date column only contain Month and Year? Can you please also show your expected output in a sample column 4? – mkRabbani Oct 06 '21 at 03:53
  • It's a full timestamp such as 4/12/2021 8:00:00 AM Output is simply the name of the user and a 'new individual' flag. Works fine now but the logic I posted above is checking ALL time based on the min login date, where I want to be able to restrict it to a certain date - such as "everyone is considered a new log-in as of mm/dd/yyyy. – Insan3 Oct 06 '21 at 04:21
  • So you need USER and DAY wise first time login flag? Please update your sample data with original Date value. – mkRabbani Oct 06 '21 at 06:00
  • Yes, something to determine that a user has logged in for the first time over a year. So if I use a date slicer and pick January 2020 - I would see Sam just for that month and never again after because that was the first time Sam signed in. My DAX currently does that, but I want to reset these values based on a date. For example, the current DAX will ensure Sam never shows up in future months, but I want to reset it every year so when Sam signs in for the first time AFTER a date a define (like a fiscal year) he will show up again, but just for that month. – Insan3 Oct 06 '21 at 18:46

1 Answers1

0

Not the answer I was looking for, but for anyone coming across this I solved it in a more traditional way. I added a new FiscalYear calculated column that basically says "if date = x, set year to y".

I then used that calculated column as a filter on the report, keeping my original DAX intact, and allowing me to switch between years on the report while keeping all the 'new' login data in place.

Insan3
  • 37
  • 7
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 12 '21 at 05:41