cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
powerbinoob31 Frequent Visitor
Frequent Visitor

Need Help! im trying to visually display the many expiry dates for an item (examples inside)

Hi Guys,

 

Ive been asked to visually show a lot expiry dates of an asset,

 

Ive been playing around with a the custom calanders but im not sure if i can do that or if i should be trying something else or if im doing it wrong (im semi new to powerbi)

 

Id like to build something like this (my data is also in the same format however its from sharepoint online)

 

forum.png

 

and id also like to build a calander with all the dates displayed on a calander and i can click on the date for all the info such as what type of expiry and asset etc.

 

forum2.png

 

Could anyone help me out, there is zero tutorials or guides i can find on this

 

Thankyou

2 REPLIES 2
AmanBedwal Frequent Visitor
Frequent Visitor

Re: Need Help! im trying to visually display the many expiry dates for an item (examples inside)

@powerbinoob31  There are two parts to your problem I have tried to individually address them.

 

1) Table with Multiple expiry dates which is conditionally formatted :

To create a visual which is similar to what is shown in the excel snapshot, you need to create a measure for each column (expiry date).

Purpose of these measures is to calculate the difference between Today’s date and date of Warranty Expiry (other columns also follow same logic)

Warranty Expiry days = DATEDIFF(TODAY(),MAX('Data Table'[Warranty Expiry]),DAY)

Booty Expiry days = DATEDIFF(TODAY(),MAX('Data Table'[Booty Expiry Date]),DAY)

 

Now above measures will provide the days difference which will be used for conditional formatting. Next, you need to apply conditional formatting for an individual column

 

SS 1.png

SS2.png

You should be able to get a similar result.

 

SS3.png

2) Creating a calendar visual: You need to first create a DateTable which has continuous dates in it. Now create a relationship between newly made Datetable Date field and each expiry date as shown below.

 

SS4.png

 

Note the cardinality and direction of the relationship. The solid line will always be the active relationship and the dotted one is an inactive relationship that can be made active using DAX.

 

For an active relationship, create the measure in the following way.

Warranty assets = CALCULATE(COUNTROWS('Data Table'))

 

I have aggregated the measure to show the number of asset which will be expired you can change it according to your need.

 

For inactive relationships create the measure in the following way.

 

Booty expiry assets = CALCULATE(COUNTROWS('Data Table'),USERELATIONSHIP(DateTable[Date],'Data Table'[Booty Expiry Date]))

 

Break test expiry assets = CALCULATE(COUNTROWS('Data Table'),USERELATIONSHIP(DateTable[Date],'Data Table'[Brake test expiry date]))

 

Follow similar logic for all dates. By doing this we have mapped all expiry date to single date column in DateTable[Date] field. Now in your calendar visual keep DateTable[Date] field as Date and rest of the fields as shown in the snapshot.

 

SS5.png

 

I hope this solves both the queries you asked.

 

Did I answer your question? Mark my post as a solution.

 

PS: If you want to know how to create DateTable you can find a lot of tutorials on the internet. If you still face a challenge in creating one let me know I will provide steps to create a Date table. Also, do not forget to mark it as Date Table under the modeling section.

powerbinoob31 Frequent Visitor
Frequent Visitor

Re: Need Help! im trying to visually display the many expiry dates for an item (examples inside)


@AmanBedwal wrote:

@powerbinoob31  There are two parts to your problem I have tried to individually address them.

 

1) Table with Multiple expiry dates which is conditionally formatted :

To create a visual which is similar to what is shown in the excel snapshot, you need to create a measure for each column (expiry date).

Purpose of these measures is to calculate the difference between Today’s date and date of Warranty Expiry (other columns also follow same logic)

Warranty Expiry days = DATEDIFF(TODAY(),MAX('Data Table'[Warranty Expiry]),DAY)

Booty Expiry days = DATEDIFF(TODAY(),MAX('Data Table'[Booty Expiry Date]),DAY)

 

Now above measures will provide the days difference which will be used for conditional formatting. Next, you need to apply conditional formatting for an individual column

 

SS 1.png

SS2.png

You should be able to get a similar result.

 

SS3.png

2) Creating a calendar visual: You need to first create a DateTable which has continuous dates in it. Now create a relationship between newly made Datetable Date field and each expiry date as shown below.

 

SS4.png

 

Note the cardinality and direction of the relationship. The solid line will always be the active relationship and the dotted one is an inactive relationship that can be made active using DAX.

 

For an active relationship, create the measure in the following way.

Warranty assets = CALCULATE(COUNTROWS('Data Table'))

 

I have aggregated the measure to show the number of asset which will be expired you can change it according to your need.

 

For inactive relationships create the measure in the following way.

 

Booty expiry assets = CALCULATE(COUNTROWS('Data Table'),USERELATIONSHIP(DateTable[Date],'Data Table'[Booty Expiry Date]))

 

Break test expiry assets = CALCULATE(COUNTROWS('Data Table'),USERELATIONSHIP(DateTable[Date],'Data Table'[Brake test expiry date]))

 

Follow similar logic for all dates. By doing this we have mapped all expiry date to single date column in DateTable[Date] field. Now in your calendar visual keep DateTable[Date] field as Date and rest of the fields as shown in the snapshot.

 

SS5.png

 

I hope this solves both the queries you asked.

 

Did I answer your question? Mark my post as a solution.

 

PS: If you want to know how to create DateTable you can find a lot of tutorials on the internet. If you still face a challenge in creating one let me know I will provide steps to create a Date table. Also, do not forget to mark it as Date Table under the modeling section.


Perfect, Thankyou so much for your reply just a quick question. 

 

I was able to achieve what i wanted by following your beautiful instructions (thankyou), but am i able to click on the calander and see what asset has what expiry on it? ie. if Asset01 and Asset09 was due today can i somehow expand on it by clicking on the date and it shows what assets are expiring? at the moment i can only see that something is expiring and seperate the type by color? 

 


@AmanBedwal wrote:

Note the cardinality and direction of the relationship. The solid line will always be the active relationship and the dotted one is an inactive relationship that can be made active using DAX.

 

For an active relationship, create the measure in the following way.

Warranty assets = CALCULATE(COUNTROWS('Data Table'))

 

I have aggregated the measure to show the number of asset which will be expired you can change it according to your need.

 

For inactive relationships create the measure in the following way.

 


 

Also for my own learning what do you mean by aggregating the measure? and with the relationships you talked about active and inactive? does it matter which dates i make the active one? im confused to "the cardinality and direction of the relationship" 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 450 members 4,332 guests
Please welcome our newest community members: