Handling table (visual) with multiple date fields

[siemdevlieger]

Hello,

I am facing an issue for which I have not yet found a resolution, hope someone here can help.

Point of view:

Working on a dashboard for tenders, for each tender we have a 'submission date' and an 'award date' which can be multiple months apart from each other.

Issue:

In my report I have a year and period filter which are linked to submission date. I have 2 calculated measures calculating the 'submitted tenders' and 'awarded tenders' for a selected period. For the awarded tenders I am using the userelationship to work with the 'award date'. However, I would also like to show a listing of the awarded tenders in a table visual for the selected period. As the selected period is linked to 'submission date', the listing is not showing tenders that have been awarded in the selected period, but were submitted in another period.

What would be a good workaround to have this solved? I could create a separate table with awarded tenders and bring in 'award date' as submission date, but was hoping to have another standard solution here.

Thanks in advance!

[Anonymous]

Hi, @siemdevlieger 

Thanks for the reply from Idrissshatila, please allow me to provide another insight:

Instead of creating a separate table or visual, you can create a dynamic measure that switches contexts based on user choice, or determines other logic to display data based on Submit Date or Grant Date. This can be achieved by using DAX expressions, such as and dynamically adjust the date field for measure calculations.

For example, you can create a measure that checks whether a user has opted in to view Submitted Bids or Awarded Bids, and then calculates the count based on the corresponding date field. Here's a simplified version of this measure:

Dynamic Tender Count = 
SWITCH(
    SELECTEDVALUE(YourSelectionColumnName, "Default"),
    "Submitted", CALCULATE(COUNT(YourTenderTable[TenderID]), USERELATIONSHIP(YourTenderTable[SubmissionDate], YourDateTable[Date])),
    "Awarded", CALCULATE(COUNT(YourTenderTable[TenderID]), USERELATIONSHIP(YourTenderTable[AwardDate], YourDateTable[Date])),
    CALCULATE(COUNT(YourTenderTable[TenderID]), USERELATIONSHIP(YourTenderTable[SubmissionDate], YourDateTable[Date]))
)

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[siemdevlieger]

Hi @Anonymous ,

thanks for your feedback, however it's not exactly what I am looking for yet. I was able to work with the measures, and your view is adding a nice functionality which I might be able to use in a different way.

I am looking however to show 2 lists in my report. One of the tables should show a list with all submitted tenders in the period. The other list should be a list (not a measure but a list with all tenders) with all awarded tenders that were won on this period (based on award_date).

That's the part I am struggling with. Below I tried to describe how it should work. ANy other suggestions are welcome.

[Idrissshatila]

Hello @siemdevlieger ,

you need to have a dim date table that is linked to both dates, one of them should be active and another should be inactive because you can have one active relationship between the tables.

then you activate the second relationship in the dax measure using the Userelashionship dax function.

check this https://youtu.be/eiDWMqgKlZI?si=rcaBOz82gHaUz0PF

[siemdevlieger]

Hi @Idrissshatila ,

first of all thanks for your reply and taking the time here.

I already use this solution in my measures, and this is working fine. However, I would now like to show 2 lists in my report. One of the tables should show a list with all submitted tenders in the period. The other list should be a list (not a measure but a list with all tenders) with all awarded tenders that were won on this period (based on award_date).

That's the part I am struggling with. Will need to see if the other opted solution might work, see below what I am trying to do 

Thanks again!

Best regards,

Siem