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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
briceem
Frequent Visitor

Generate dynamic boolean column from calcul between a column and a date selection from a Slicer

Hi,

I have the following matrix:

briceem_1-1692608404663.png

 

-1 column for Created Date

-1 column for Closed Date 

-Bunch of others column for other information (out of scope for this thread).

 

I also have a slicer menu based on the Created Date. This slicer allows to select one or multiple months to filter on created date (with date hierarchy).

-> If we selected "May 2023" in this slicer, we will have all the lines with Created Date between 1st May and 31th May 2023.

 

 

Now, what I am trying to do, is having a new Column displayed on this table "Closed At Last Selected Month". 

 

So I guess I need to

 

1. create a Measure to get the current Selected Date from the slicer and calculate the end of month

-> 

TestMaxSelectedDate = EOMONTH(MAX(Table[CreatedDate]),0) + TIME(23,59,59)
It works fine if I display this value in a Map:

 

2. create another Measure (or maybe a column?) to have my boolean value with something like:

IsClosedAtSelectedDate = [TestMaxSelectedDate] > Table[ClosedDate]
-> it doesn't work at all and I can't tell why.
 
Moreover, if I add my TestMaxSelectedDate in the table displaying the list, I got strange result. 
Please see below if I am selecting the month May:
 
briceem_2-1692611525882.png

 

As we can see, the Measure from the map is correct, but in the table we have like a null value ? 30/12/1899 is like the epoch time in PowerBI, right ?

And the boolean is doing... well, I don't know but certainly not my expected result !

 

2nd example, if I select nothing in my slicer:

briceem_3-1692611677542.png

 

 

Is it possible to achieve what I'm trying to do ? I found a lot of examples but for calculating a value (like sum of Sales at selected date, etc.)

 

Thanks in advance

 

 

Edit: looks like I find a way to achieve something:

 

1. create a Measure to get the current Selected Date from the slicer and calculate the end of month

-> 

TestMaxSelectedDate = EOMONTH(MAX(Table[CreatedDate]),0) + TIME(23,59,59)

2. Create a measure based on seletecd value to store the ClosedDate:

MeasuredClosedDate = IF(SELECTEDVALUE(Table[ClosedDate]),SELECTEDVALUE(Table[ClosedDate]), DATE(2999, 12, 30) )

3. Create a column between thow two measures

IsClosedAtSelectedDate = [TestMaxSelectedDate] > [MeasuredClosedDate]
1 REPLY 1
lbendlin
Super User
Super User

You cannot create a calculated column or calculated table from a measure.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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