Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a dataset which has a number of customers and their sales. There is a date column but it does not contain all dates, but just dates for which a sales was made.
I am trying to make a visual where it would show me the sale from the middle date of the month for every month on a bar graph.
I tired using this
measure=FIRSTDATE(Table1[Date2]) + INT(DATEDIFF(FIRSTDATE(Table1[Date2]),LASTDATE(Table1[Date2]),DAY) / 2)
but it picks the exact middle of the month date, but does not consider if that date even exists on my table.
Solved! Go to Solution.
In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...
In your case, I would create a date table using CALENDAR or CALENDARAUTO and use that for your axis. Then you could write a measure something like:
Measure 2 = VAR __date = MAX(Calendar[Date]) RETURN IF(__date = [Measure]),<DO YOUR CALCULATION HERE>,BLANK())
So basically check if the current date is the middle of the month ([Measure]) and if so, do your calculation of what you want to return, otherwise BLANK().
Here is one method:
Measure = VAR __date = MAX([Date]) VAR __month = MONTH(__date) VAR __year = YEAR(__date) VAR __table = FILTER(ALL('Table'),MONTH([Date]) = __month && YEAR([Date]) = __year) VAR __middle = (EOMONTH(__date,0) - DATE(__year, __month, 1) + 1) / 2 VAR __table1 = ADDCOLUMNS(__table,"__diff",ABS(DATE(__year,__month,ROUNDUP(__middle,0)) - [Date])) VAR __mid = MINX(__table1,[__diff]) RETURN MAXX(FILTER(__table1,[__diff] = __mid),[Date])
This returns the date closest to the middle of the month that actually exists in the table. See attached.
@Greg_Deckler , this works really well Greg, but i am not able to use the measure on the axis for any charts. Is there a reason for that?
@Greg_Deckler If you have the time, could you please help me out on how to be able to use this measure on an axis?
In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...
In your case, I would create a date table using CALENDAR or CALENDARAUTO and use that for your axis. Then you could write a measure something like:
Measure 2 = VAR __date = MAX(Calendar[Date]) RETURN IF(__date = [Measure]),<DO YOUR CALCULATION HERE>,BLANK())
So basically check if the current date is the middle of the month ([Measure]) and if so, do your calculation of what you want to return, otherwise BLANK().
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |