Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Change tracking table to Monthly summary table

Hi,   I have my dataset in the following format: AssetID Type Status Count Date A1 Sales Active 1 1/01/2018 A2 Rental Active 1 5/01/2018 A3 Sales Active 1 10/01/2018 ...
Need Help
TomMartens's avatar
TomMartens
Icon for Super User rankSuper User
7 years ago

Hey, 

 

just create a dedicated Calendar table, this is a simple example (using DAX):

Calendar = 
var datestart = MIN('Table2'[Date])
var dateend = MAX('Table2'[Date])
return
ADDCOLUMNS(
    CALENDAR(datestart , dateend)
    , "Year" , FORMAT(''[Date] , "YYYY")
    , "Year - Month" , FORMAT(''[Date] , "YYYY-MM")
)

Here you will find much more detailed guidance how to create a very detailed calendar table: https://www.sqlbi.com/tools/dax-date-template/

Nevertheless, just create a relationship between your table like so:

Now it becomes quite simple to create the table visual you are looking for, just by using the columns from the tables.

 

Regards,
Tom

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks Tom.

    I have created the date table and linked to data table. Should I create a calculated table or just calculated columns for what I need?

    Any chance you can share pbi file if you have it.

    Thanks.