Forum Discussion
Calculation Filter working when using Today Function, but not when I use a calculated date
Hi again cjbaguley
Thanks for sharing the PBIX!
I had a look and the measure itself actually seemed to be working correctly, however some visual interactions were disabled in the report page, which meant the card visual wasn't updating based on filters.
Nonetheless, I would recommend these other adjustments which I've made in the attached PBIX:
1. Added columns Offset Min and Offset Max to Lender Start Date Slicer:
2. Rewrote Lender Period Count to make use of these columns:
Lender Period Count = VAR SelectedDate = [Selected Date] VAR SingleFilter = HASONEFILTER ( 'Lender Start Date Slicer'[Days Since Start Date] ) VAR OffsetMin = IF ( SingleFilter, SELECTEDVALUE ( 'Lender Start Date Slicer'[Offset Min] ), -365 ) VAR OffsetMax = IF ( SingleFilter, SELECTEDVALUE ( 'Lender Start Date Slicer'[Offset Max] ), 0 ) RETURN CALCULATE ( COUNT ( 'Lender Details'[LenderEmpNoText] ), KEEPFILTERS ( 'Lender Details'[Lender Role Start Date] >= SelectedDate + OffsetMin && 'Lender Details'[Lender Role Start Date] <= SelectedDate + OffsetMax ) ) + 0The measure will apply a filter according to the single date range selected. If there are multiple or no selections made, the default offset is [-365,0].
3. I also added a visual-level filter to the table visual: Lender Period Count ≠ 0.
This seems to work as intended. Is this what you're looking for?
Thanks again Owen, apologies for the delay in reply, long weekend here and other priorities this week.
I think I have been able to replicate my pbix properly without real data and uploaded ot my dropbox Sample Lender Details pbix
I did try the debugging you suggested above, and while I could get it to work filtering anything >= Min(Date) (showing all 23 sample lenders, when I tried to do <= Min(Date) I get blanked out, when I'm expecting it to show only the 7 lenders who meet that criteria.
Hi again cjbaguley
Thanks for sharing the PBIX!
I had a look and the measure itself actually seemed to be working correctly, however some visual interactions were disabled in the report page, which meant the card visual wasn't updating based on filters.
Nonetheless, I would recommend these other adjustments which I've made in the attached PBIX:
1. Added columns Offset Min and Offset Max to Lender Start Date Slicer:
2. Rewrote Lender Period Count to make use of these columns:
Lender Period Count = VAR SelectedDate = [Selected Date]
VAR SingleFilter = HASONEFILTER ( 'Lender Start Date Slicer'[Days Since Start Date] )
VAR OffsetMin = IF ( SingleFilter, SELECTEDVALUE ( 'Lender Start Date Slicer'[Offset Min] ), -365 )
VAR OffsetMax = IF ( SingleFilter, SELECTEDVALUE ( 'Lender Start Date Slicer'[Offset Max] ), 0 )
RETURN
CALCULATE (
COUNT ( 'Lender Details'[LenderEmpNoText] ),
KEEPFILTERS (
'Lender Details'[Lender Role Start Date] >= SelectedDate + OffsetMin
&& 'Lender Details'[Lender Role Start Date] <= SelectedDate + OffsetMax
)
)
+ 0The measure will apply a filter according to the single date range selected. If there are multiple or no selections made, the default offset is [-365,0].
3. I also added a visual-level filter to the table visual: Lender Period Count ≠ 0.
This seems to work as intended. Is this what you're looking for?
This nailed it!! Thank you so much OwenAuger!!! Weeks of frustration sorted.
I still had a little diffculty when replicating in the real pbix, however once I realised that I had inadvertantly left the relationship between master date table and lender details blank in the sample and replicated in the real, it all fell into place!!!
A second filter to ensure it was only lenders after the start date I wanted across the whole data source and I was off, and able to replicate your much more elegant measure for the other metrics being calculated off the basis of the lender start date.
Absolute star!!!!