Calendar

[LearnToFly]

If you are working with a table in Power BI that contains three different date fields... a date completed, date filled, and date written but you need to build a table based on completed for one and another table for date written what is the best way to accomplish this?  I have created a separate calendar and have one relationship active, but it only allows one and there are times in my reporting in which I need to switch which date I am pulling from.

[anmolmalviya05 · Accepted solution]

Hi @LearnToFly , Hope you are doing good!

To manage multiple date fields in Power BI, create a calendar table with an active relationship to Date Completed and inactive relationships to Date Filled and Date Written. Use the USERELATIONSHIP DAX function to activate these relationships in measures. For example:

Total Completed = SUM(FactTable[Value])
Total Filled = CALCULATE(SUM(FactTable[Value]), USERELATIONSHIP(FactTable[DateFilled], Calendar[Date]))
Total Written = CALCULATE(SUM(FactTable[Value]), USERELATIONSHIP(FactTable[DateWritten], Calendar[Date]))

Example of Using Slicers to Switch Dates

You can also add a slicer or parameter to allow users to switch between these date fields dynamically. This can be done using a disconnected table and SWITCH function.

Create a Date Selection Table:
Create a table with options for each date field. For example:

DateSelection
Date Completed
Date Filled
Date Written

Create a Measure to Select Date:
SelectedDateMeasure =
SWITCH (
SELECTEDVALUE(DateSelection[DateSelection]),
"Date Completed", [Total Completed],
"Date Filled", [Total Filled],
"Date Written", [Total Written],
BLANK()

Add Slicer and Use Measure:
Add the DateSelection column as a slicer to your report and use the SelectedDateMeasure in your visuals.

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

[Greg_Deckler]

@LearnToFly You have basically 2 options. You can create multiple relationships and then use USERELATIONSHIP in measures or you can create 3 separate calendar tables. OK, a third option is to just ditch the calendar table and just straight use the date columns in the fact table but some folks would frown on that approach although it would work perfectly fine for the majority of scenarios.

[LearnToFly]

Thanks, Greg, I did try to do the USERELATIONSHIP option however I also needed to slice on it.  Therefore, I guess my only option is multiple calendars...I just really haven't seen others using that approach but will try it out of necessity.  Thanks for the help!