Forum Discussion
Generate daily values from date range
Hey guys, I need help with this:
I have data that looks like this:
Date ID Status
2019.01.15 USER1 NEW
2019.01.30 USER1 REGISTERED
2018.11.17 USER2 NEW
2019.01.18 USER2 REGISTERED
2019.01.19 USER3 NEW
2019.01.26 USER3 REGISTERED
2019.01.30 USER3 ACTIVE
2019.03.22 USER3 SUSPENDED
2019.03.24 USER3 ACTIVE
2019.01.24 USER1 ACTIVE
2019.01.25 USER4 NEW
2019.02.26 USER1 SUSPENDED
2019.04.21 USER3 SUSPENDED
What I'd like is a daily account of all users existing by that date with their status, something like this (not counted from the data above, just for example):
Date ID Status
2019.01.01 USER1 NEW
2019.01.01 USER2 NEW
2019.01.02 USER2 REGISTERED
2019.01.02 USER1 REGISTERED
2019.01.02 USER3 NEW
2019.01.03 USER3 REGISTERED
2019.01.03 USER1 ACTIVE
2019.01.03 USER4 NEW
2019.01.03 USER2 SUSPENDED
I'd ideally do this in DAX, also ideally for just the last 1 month (there is a daily refresh in the database). I started creating a calendar table for the desired time period and do a join, but it won't work the way intended.
Any ideas? Many thanks in advance!
1 Reply
With your Calendar Table related to your Fact Table, use the Date column from the Calendar Table on rows and ID from your Fact table on columns. Then use the following measure:
Status = VAR __LastMonth = EDATE( TODAY(), -1 ) RETURN IF( AND( MAX( DimCalendar[Date]) >= __LastMonth, HASONEVALUE( Table1[Status]) && HASONEVALUE( DimCalendar[Date])), VALUES( Table1[Status] ))Will only show statuses and associated ID's that have occured within the last month from the current date:
Final table ( I added another record for april to make sure it was working correctly)