Change tracking table to Monthly summary table

[Anonymous]

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
A1	Sales	Inactive	0	15/02/2018
A4	Rental	Active	1	15/02/2018
A3	Sales	Inactive	0	11/03/2018
A5	Rental	Active	1	10/04/2018
A6	Sales	Active	1	10/06/2018
A2	Sales	Active	1	10/07/2018

I would like to turn this into following format, so I can do month on month comparision:

Month	Type	Count
Jan-18	Sales	2
Jan-18	Rental	1
Feb-18	Sales	1
Feb-18	Rental	2
Mar-18	Sales	0
Mar-18	Rental	2
Apr-18	Sales	0
Apr-18	Rental	3
May-18	Sales	0
May-18	Rental	3
Jun-18	Sales	1
Jun-18	Rental	3
Jul-18	Sales	2
Jul-18	Rental	2

I am failry new to DAX. I would like to know if this is possible with DAX.

Note that there may be gaps in the data,, i.e. no changes happened for entire month, however we still need to report on that month. Also Asset type can also change over time (last record).

Thanks,

Navap

[TomMartens]

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]

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.