Monthly report based on user's activities

[koorosh]

Hello All, I have the following table for users for each month:

What is the optimum approach to get the following table for creating visual charts ?

Should I append all tables to each other? Should I make the relation between each table and the date table? Should I create many date tables to make a relation with each date type column in the tables?

[koorosh]

https://docs.google.com/spreadsheets/d/1T1Makr1wbIxWN9-KkMwoZmd2Av0PDsWW/edit?usp=sharing&ouid=11435...

[Ahmedx]

tell me you have one file or there will be many every month, if every month send another file from another month

and another question, do you need only the last 6 columns? as in the screenshot

[koorosh]

https://drive.google.com/file/d/1U1l2CxUGf52ErKOuQaxbkpd3XkymsE_s/view?usp=sharing

Please, also let me know what is a better data-gathering method compared to the one seen in the Excel file.

[danextian]

Hi @koorosh ,

There is lesser work to be done in the query  editor if the data is one sheet but there is hardly a difference in query performance if there are only a few thousand rows. That being said, there are two queries in attached pbix - one that combines each month of data and once which just connects to a single worksheet.  Note: I used December data for November by replacing dates.

[koorosh]

Thanks, Dan, I got the following error:

Could you please how you created the date table? Usually, I use the following code:

[danextian · Accepted solution]

The column name is based on the sample data you provided:

Check whether your actual data has this column or it might have been renamed.  M is case sensitive - users <> Users. It is important when posting a sample data that you use the actual column names that will be in your data source. Replace "users" in the code with the correct column name.

The dates table was created in DAX.  You can just use your own calendar  - either import or generated using M but you'll need to rebuild the relationships and point the measures to the correct table/columns.

[danextian]

Hi @koorosh ,

Assuming that the month and year relates to the dates in each column and not just a single one, I would change the data type of timestamp columns to date, create a separate dates table, create an active relationship with column C and inactive ones with the other columns and use USERELATIONSHIP in a measure to invoke the active relationship in a measure. I would have attached a sample pbix if I could copy and paste the sample data to an Excel file. Alas, it is an image.