Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
FalkoDroege
Regular Visitor

Period-based evaluation

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

5 REPLIES 5
tamerj1
Super User
Super User

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.

  

FalkoDroege
Regular Visitor

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:

idtypeWarranty till
1Device 101.01.2022
2Device 212.02.2022
3Device 101.03.2022
4Device 201.01.2022
5Device 326.02.2022
6Device 101.03.2022
7Device 201.01.2022
8Device 328.02.2022
9Device 101.03.2022
10Device 304.02.2022
5Device 326.02.2022
6Device 101.03.2022
7Device 201.01.2022
8Device 328.02.2022
9Device 101.03.2022
10Device 304.02.2022

 

wished result:

month yeartypedevices in warranty
Jan 22Device 14
Jan 22Device 23
Jan 22Device 33
Feb 22Device 13
Feb 22Device 21
Feb 22Device 33
Mrz 22Device 13
Mrz 22Device 20
Mrz 22Device 30

 

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

 

 

daXtreme
Solution Sage
Solution Sage

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors