Date grouping by Start and End date

[Sashwato]

Hello everyone!

I had a question. We need to be able to take a Job Delivery date (2nd screenshot) and compare it to the Perdebut  (start date) and Perfin (end date) on the calendar to determine the fiscal year and period. 

A single table may have multiple dates of interest to different stakeholders.  The table we look at below, for example, has 3 dates that may be used to create various reports.  We want to give the user the freedom to group the data using whatever date is appropriate for them.

Please let me know your suggestions. Any ideas are welcomed!

Regards!

[mahoneypat]

You should convert your DateTime columns to just Date, add a Date table to your model, and make a relationship between it and each of the 3 date columns in your table.  You can then make 3 measures each using a different relationship to filter your table (using the columns from the Date table in the visual).  Since only 1 relationship can be active at a time, you will need to use USERELATIONSHIP in 2 of your measures to activate that relationship for that calculation.

You can then use a disconnected table with 3 words/phrases to be used in a slicer for the user to select the desired view/calculation.  A measure that uses SWITCH to return 1 of the 3 measures based on the slicer selection will also be needed.

Pat

[Sashwato]

Pat,

Thank you for your suggestion. Instead of adding the new date table, can we make use of the above screenshot - start date and end date table?

Essentially we need to compare the Job Delivery Date with the start and end date present on a different table as displayed on screenshot 1 and then display the data.

[v-kelly-msft · Accepted solution]

Hi  @Sashwato ，

Create a column similarly as below:

JobDateDelivery = CALCULATE(MAX('Table'[JobDateDelivery]),FILTER('Table','Table'[JobDateDelivery]>='Table (2)'[Predebut]&&'Table'[JobDateDelivery]<='Table (2)'[Perfin]))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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