Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
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
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
79 | |
64 | |
52 | |
48 |
User | Count |
---|---|
217 | |
89 | |
76 | |
67 | |
60 |