Handling Flexible Time Periods and Customer-Exclusion Comparisons in Power BI

[AT87]

Hi Power BI Community,

I’m building a dashboard to share with external customers, and I’m facing a couple of challenges that I haven’t encountered before. I’d appreciate any guidance or best practices from your experience.

Challenge 1: Flexible Time Periods

• The dashboard should allow the customer to select one of three possible time periods:

  1. Calendar Year (Jan–Dec)

  2. My company’s fiscal year (July–June)

  3. Customer-specific contract year (which can vary, e.g., Apr–Mar, Sep–Aug, etc.)

• Based on the selected calendar, the following calculations should update dynamically:

  • YTD values

  • Month-over-month growth

  • Trend charts

• I have not worked on such a dynamic time-period selection scenario before. How can I implement this in Power BI? Should I create a custom date table per customer, or is there a DAX pattern for handling dynamic fiscal/contract years?

Challenge 2: Comparing KPIs Across Other Customers

• Customers want to see their KPIs compared to the consolidated KPIs of all other customers, excluding themselves.

• For example, Customer A wants to compare KPI1 (sum) against all other customers.

• How should I handle this dynamically in DAX for:

  • Sum KPIs

  • Average KPIs

  • Growth metrics (YoY, MoM, etc.)

Any tips, patterns, or sample DAX formulas for these scenarios would be hugely helpful.

[v-tsaipranay]

Hi @AT87 ,

I wanted to follow up on our previous suggestions. We would like to hear back from you to ensure we can assist you further.

Thank you.

[AT87 · Accepted solution]

Hi @v-tsaipranay - I tried an alternative approach where I created a table with the list of customers and the month number from where the contract will start. Then I used DAX queries to identify the month and to derive a calculated column for the different year types in my fact table corresponding to the dates. Then I used these derived column as a slicer on the dashboard to slice the KPIs across different time periods (years) and it worked. 

[v-tsaipranay]

Hi @AT87 ,

Thank you for letting us know. I'm glad it worked. Thank you for using the Microsoft Fabric Community Forum. If you have any further questions, please feel free to reach out we're happy to assist you.

[AT87]

Hi @v-tsaipranay  Thanks for sharing the file and I can see that you have FiscalYear and ContractYear columns in your Date Table.

Now the challenge is with Contract Year, since:

• You have 100+ customers.
• Each customer can follow their own contract year cycle (e.g., Apr–Mar, May–Apr, Sep–Aug, etc.).
• For reporting, you need to map each date to a Contract Year label specific to that customer.

Example Scenarios:

1. Customer A: Contract Year = Apr 2024 – Mar 2025

   • All dates from Apr 2024 to Mar 2025 should be labeled as ContractYear = 2024 (because it starts in 2024).

2. Customer B: Contract Year = May 2024 – Apr 2025

   • All dates from May 2024 to Apr 2025 should be labeled as ContractYear = 2024.

The Problem:

• In the Date Table, April 2025 would belong to:

  • ContractYear = 2024 for Customer A

  • ContractYear = 2025 for Customer B

• But the Date Table ideally has unique rows per date. It cannot have two different ContractYear values for the same date depending on customer.

This is where I am getting stuck

[v-tsaipranay]

Hi @AT87 ,

I have tried implementing a different measure in next page:

This is what I was able to gather from the requirements, and I've tried a different approach. Also, could you check in the pbix file where we have the date table? In that table, there's a ContractYear column that we can use in the slicer, which might also work for your scenario.
Below image is for your reference:

If I misunderstood the scenario, kindly provide a sample data so you can understand it better and provide a solution.
Hope this helps.

Thank you.

[v-tsaipranay]

Hi @AT87 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. 

Thank you.

[v-tsaipranay]

Hi @AT87 ,

Thank you for reaching out to the Microsoft fabric community forum. Thank you @Greg_Deckler  for your inputs on this issue.

After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used sample data on my end and successfully implemented it.     

I am also including .pbix file for your better understanding, please have a look into it:
Hope this helps. Please feel free to rech out for any further questions.

Thank you .

[AT87]

@Greg_Deckler Thanks a lot for your inputs! That really helps me think in the right direction. 🙏

Would it be possible for you to share a sample PBIX file (with dummy data) that shows how you’ve set it up? That would make it much easier for me to understand the exact approach and apply it to my case.

[Greg_Deckler]

@AT87 I can't, they don't let me upload PBIX files.

[Greg_Deckler]

@AT87 For 1, I highly recommend that you implement a Date/Calendar table that uses offsets. Probably have a disconnected table for them to choose their time period and then your measures account for this and calculate appropriately.

For 2, what I have seen that is generally done is that you use an aggregation table that "anonymizes" the data. You just take your fact table(s) and create aggregated versions of them by whatever grouping you want, year, month, customer segment, etc. You then use this table to base your measures on. There is generally no RLS on this table but that doesn't have to be the case if you only want customers seeing their own segments for example.