Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Good morning, everyone,
I am still a beginner in Power BI and DAX and need your swarm knowledge. I would like to recreate an old evaluation and therefore need a period-based evaluation.
The initial data looks like this (example):
id | description | Warranty till
1 | Device 1 | 01.01.2022
2 | Device 2 | 12.02.2022
3 | Device 3 | 01.03.2022
4 | Device 4 | 01.01.2022
5 | Device 5 | 26.02.2022
6 | Device 6 | 01.03.2022
7 | Device 7 | 01.01.2022
8 | Device 8 | 28.02.2022
9 | Device 9 | 01.03.2022
10 | Device 10 | 04.02.2022
I now need a list of how many devices were under warranty at which point in time:
month year | devices in warranty
___________________________________
Jan 22 | 3
Feb 22 | 4
Mar 22 | 3
I get the month year column from a date table.
Do you have any idea how I can solve my problem?
Many thanks in advance for any suggestions.
Best regards
Falko
Hi @FalkoDroege
you may try
Number Of Devices =
VAR CurrentDate =
MAX ( 'Date'[Date] )
VAR T1 =
CALCULATETABLE (
TableName,
TableName[Warranty till] <= CurrentDate,
CROSSFILTER ( 'Date'[Date], TableName[Warranty till], NONE )
)
VAR T2 =
SELECTCOLUMNS ( T1, "@Description", TableName[description] )
VAR T3 =
DISTINCT ( T2 )
RETURN
COUNTROWS ( T3 )
Hi tamerj1,
I stand in awe. Many thanks for this solution. I hope, I understand what you did and I will try to use this way for my data.
Thank you, daXtreme. Nice solution but unfortunately wrongly formulated question. Sorry, for that. I hope next is better and you can help me again.
correct base data:
id | type | Warranty till |
1 | Device 1 | 01.01.2022 |
2 | Device 2 | 12.02.2022 |
3 | Device 1 | 01.03.2022 |
4 | Device 2 | 01.01.2022 |
5 | Device 3 | 26.02.2022 |
6 | Device 1 | 01.03.2022 |
7 | Device 2 | 01.01.2022 |
8 | Device 3 | 28.02.2022 |
9 | Device 1 | 01.03.2022 |
10 | Device 3 | 04.02.2022 |
5 | Device 3 | 26.02.2022 |
6 | Device 1 | 01.03.2022 |
7 | Device 2 | 01.01.2022 |
8 | Device 3 | 28.02.2022 |
9 | Device 1 | 01.03.2022 |
10 | Device 3 | 04.02.2022 |
wished result:
month year | type | devices in warranty |
Jan 22 | Device 1 | 4 |
Jan 22 | Device 2 | 3 |
Jan 22 | Device 3 | 3 |
Feb 22 | Device 1 | 3 |
Feb 22 | Device 2 | 1 |
Feb 22 | Device 3 | 3 |
Mrz 22 | Device 1 | 3 |
Mrz 22 | Device 2 | 0 |
Mrz 22 | Device 3 | 0 |
All devices of type "device 1" are still in waranty on Jan 22. Zero devices from type "device 3" are in waranty on March.
With this data my solution still stands as it is (and you have to have a Dates dimension). If you want to see 0's, all you have to do is:
[# Devices] = DISTINCTCOUNT( YourTable[id] ) + 0
Join the Dates table on Date to the table above on Warranty Till. In your Dates table you should have all the pieces of time defined, among them a Month-Year column. Drop this column onto a visual (table/matrix). Create this measure:
[# Devices] = DISTINCTCOUNT( YourTable[id] )
and drop it onto the canvas. By the way, if there's a 1-to-1 correspondence between id and description, then you should only keep one of the fields. Drop any columns in your model you don't need as this makes it smaller and faster, also less confusing. The join from Dates to YourTable should be 1-to-many with one-way filtering.
User | Count |
---|---|
42 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |