Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I've got rows that have columns [FromTime] and [ToTime], so I'm try to create a column that shows DATEDIFF between these 2 columns, but I also want the DATEDIFF to take into account the slicer which is based off the [FromTime] column.
So if I have a [FromTime] of April 1, and a [ToTime] of April 10, but my slicer is filtering [FromTime] to show dates between April 8-12, then the DATEDIFF will only show 2 days (April 8 - April 10)
Here's some more examples of how it should calculate:
Date Filter | [FromTime] | [ToTime] | DATEDIFF |
April 1- April 5 | April 1 | April 10 | 5 |
March 28 - April 5 | April 1 | April 10 | 5 |
April 9 - April 13 | April 1 | April 10 | 1 |
Hope this makes sense!
Thanks everyone!
hi @Anonymous
Supposing you have two tables like:
they are not related.
1) try to plot a slicer with Dates[Date] column
2) try to plot a table visual with Data[From], Data[To] and a measure like:
Datediff =
DATEDIFF(
MAX(MAX(Data[From]),MIN(Dates[Date])),
MIN(MAX(Data[To]),MAX(Dates[Date])),
DAY
)+1
it worked like:
p.s. calculated columns are not responsive to slicer or any report visuals, so table visual with meausre is used here.
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
13 | |
11 | |
9 | |
6 |