Distinct daily, weekly, and monthly count over X number of years

[Anonymous]

Hello all, 

I am trying to recreate a report that looks like the one below. I need three separate lines that represent the distinct amount of daily, weekly, and monthly users all on the same graph, which spans over several years. 

I managed to recreate the graph, but it doesn't look quite as nice. The weekly and monthly lines are stepping up and down, rather than and straight line from one data point to another.

For my table, I have a Date column and a User ID column. I also created two calculated columns, Year-Mon and Year-Week.

Year-Mon = FORMAT([Date],"YYYY-MMM")

Year-Week = FORMAT([Date],"YYYY-WW")

For the 3 measures in the graph, they are as follows:

monthly = CALCULATE(DISTINCTCOUNT(Table[user_id]), FILTER(ALL(Table), Table[Year-Mon]=MIN(Table[Year-Mon])))

weekly= CALCULATE(DISTINCTCOUNT(Table[user_id]), FILTER(ALL(Table), Table[Year-Week]=MIN(Table[Year-Week])))

daily = DISTINCTCOUNT(Table[user_id])

Is there a way to make the weekly and monthly lines more like the first picture?

[v-henryk-mstf · Accepted solution]

Hi @Anonymous ,

According to your description, directly in the line chart visual, turn off the "stepped" function in Format->shape.

Such as below:

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

yes, but it will take a lot more effort.  Your best bet may be to create sliding windows instead (-3 to +3 for the week and -15 to +15 for the month)  based on the daily values.

If you like more help please provide sanitized sample data in usable format (not as a picture - inserting it into a table would be good).