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

briceem — Mon, 21 Aug 2023 10:28:03 GMT

Hi,

I have the following matrix:

-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:

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:

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]

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

lbendlin — Wed, 23 Aug 2023 01:08:04 GMT

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

topic Generate dynamic boolean column from calcul between a column and a date selection from a Slicer in DAX Commands and Tips

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

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