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.
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)
2. create another Measure (or maybe a column?) to have my boolean value with something like:
IsClosedAtSelectedDate = [TestMaxSelectedDate] > Table[ClosedDate]
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]
You cannot create a calculated column or calculated table from a measure.
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |