Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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().
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |