Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
LearnToFly
Frequent Visitor

Calendar

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.
 
 
3 REPLIES 3
anmolmalviya05
Responsive Resident
Responsive Resident

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
Super User
Super User

@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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.