Mutiple date replatioships or reporting periods

[spartan27244]

I'm trying to figuree out how to best handle multiple customer defined reporting periods. In a typical secario a customer will have their benefit year defined as Sept - Aug, and I will have a table in my SQL database for each year 2016, 2017, 2018, etc. in addition, that same customer my have an additional reporting periods all with different start and end dates and over multiple years. Now multiply that over many customers. How can I represent this in PowerBI. I am currently doing this as a table that has an Id to represent the customer, A name for the period i.e. 2016 BY, 2017 BY... and a date column populated with every day from the beginning to the end of the period. This of course creates a many to many relationship, and cannot be marked as a DATE table. Looking for ways around this before it becomes something that cannot be managed. Thanks

[spartan27244 · Accepted solution]

After many hours and some luck I finally figured it out. Step one is to create a standard Date table to filter your data, then relate the date table to you report period dates. Boom!

[v-yangliu-msft]

Hi  @spartan27244  ,

Here are the steps you can follow：

1. Create calculated table.

Date = CALENDARAUTO()

2. Create measure.

Flag =
var _min=MIN('Date'[Date])
var _max=MAX('Date'[Date])
return
IF(_min>=MAX('Table'[StartDate])&&_max<+MAX('Table'[EndDate]),1,0)

3. Use the Date column of the Date table as the slicer, put measure[Flag] into the Filter, and select is = 1

4. Result:

When the slicer dates are 2016-03-21 and 2016-09-18, the following results are displayed in the visual object:

Best Regards,

Liu Yang

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

[spartan27244]

One idea that I thought of was use a slicer to allow the user to select the report period in the  period table then use that to get SelectedValue of the Start and End Date and then supply these values to a Date Slicer as a beginning and end date. However I do not know of a way to populate a visual with data based another's visual's selection

[ChrisMendoza]

@spartan27244 - sounds like you have a similar need to https://community.powerbi.com/t5/Desktop/Multiple-Calendars-for-Multiple-Companies/m-p/901130#M43190... . Try that method.

[spartan27244]

I cannot tell from that post that anything was solved, and I do not see an actual solution. Looks like the poster simply marked their own comment as a solution.

[spartan27244]

The data does not just pertain to one customer, that is a different filter. The report is not just loaded for one customer, but for all, so the user must be able to put in parameters to provide rport periods, or we must be able to adapt the ones defined in our normal SQL database.  In our SQL database it looks like this.

PeriodId	PopulationId	PeriodName	StartDate	EndDate
1	A	2016-BY	9/1/2016	8/31/2017
2	A	2017-BY	9/1/2017	8/31/2018
3	B	2019 -FY	3/1/2016	2/28/2017
4	C	2018-CY	1/1/2018	12/31/2018

So as you can see from above each period has a unique key, the a book of business has a key (PopulationId) and there are varying Start and End Dates. Each Book of busienss is owned by a customer and a single customer can have many populations. So to represent this as a simple date table with "tags" for the data ranges is almost impossible. Currenty I am joining this table to a date table to produce all of the multiple dates in the ranges for each period row above. This leades to a table with M-M relationships.

[ChrisMendoza]

@spartan27244 - The 'date' is still a date though. Each of your customers just report it how they need. So you could just create Calculated Columns in PBI or use Power Query Custom Columns before you load to PBI.

Essentially you would be doing something like:

psuedo-SWITCH; the if monthNum = #, then newMonthName

This shows how I'm moving MonthNum = 1 ('Jan') to newMonthName = ('Jul')

  {1, "Jul"},
  {2, "Aug"},
  {3, "Sep"},
  {4, "Oct"},
  {5, "Nov"},
  {6, "Dec"},
  {7, "Jan"},
  {8, "Feb"},
  {9, "Mar"},
  {10, "Apr"},
  {11, "May"},
  {12, "Jun"},
  {input, "Undefined"}

 You can do similiar logic for Fiscal Years

if [MonthNum] >= 7 then Date.Year([Date]) - 1 else Date.Year([Date])

I only have Power Query example but the technique could be transferred to DAX.