DAX Date Range Summary

[akos_skutovics]

Does anyone have effective DAX code for summarizing a selected date range in an easily readable format for both continuous and non-continuous periods?

I typically use a slicer with these columns:

• Year (2023, 2024)
• Quarter (Q1, Q2)
• Month (Jan, Feb)

Or structured like this:

• Year (2023, 2024)
• Year-Month (2023-01, 2023-02)

Expected output examples:

• If 2023 Q4, 2024 Q1, and 2024 Q2 are selected, the output should be:
  2023 Q4 - 2024 Q2

• If 2023 Q4 and 2024 Q2 are selected, the output should be:
  2023 Q4, 2024 Q2

• If Jan 2024, Feb 2024, Mar 2024, and Apr 2024 are selected, the output should be:
  Jan - Apr 2024

• If Jan 2024, Feb 2024, Apr 2024, May 2024, and Jun 2024 are selected, the output should be:
  Jan - Feb 2024, Apr - Jun 2024

• And so on.

[MFelix]

Hi @akos_skutovics ,

Check this blog post forom SQLBI.

https://www.sqlbi.com/blog/alberto/2018/12/20/displaying-a-list-of-selected-months/

This is for months but you can adjust it for a time frame 

[lbendlin]

What if November 2023, December 2023 and January 2024 are selected?

[akos_skutovics]

I would expect Nov 2023 - Jan 2024

[lbendlin]

Sounds like a nice project. There will be lots of pitfalls (for example when someone selects 2024 Q1 and September 2023) but it will be fun to explore all the intricacies of ISINSCOPE.  Good luck.

Note: Is that something that your users asked for?  What is the business value? Why not use the filter icons?

[akos_skutovics]

When 2024 Q1 and September 2023 are selected, I would probably default back to month level:
Sep 2023, Jan-Mar 2024.

The use case is to display a summary of what is shown on a page in combination with other slicers and description of the main visuals, for easy understandability for people who does not have a lot of experience with consuming Power BI content.

What would be the most optimal way to tackle this?

[lbendlin]

What would be the most optimal way to tackle this?

The most optimal way would be to not do it.  Instead, educate your users about the visual filter icon.

[akos_skutovics]

I understand your point, but I have to disagree with this perspective. If a user wants to gain insights more quickly and efficiently, I don't think it's fair to respond with, "you just need to improve and think faster."

Not to mention other scenarios when the insights need to be shared with others, or for pages with several dropdown slicers.

[lbendlin]

yes, we can agree to disagree.

 or for pages with several dropdown slicers.

This, so much this.  Why waste screen real estate with slicers when you could use the filter pane?

[akos_skutovics]

because filter pane is not dynamically filtered, and slicers are faster. not to mention multi-level slicers.

[MFelix]

There is also this video with this solution from Alberto Ferrari:

https://www.youtube.com/watch?v=QYbl4k_CWBE