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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
galbatrox9
Helper I
Helper I

Middle of the month date if that date exist in dataset [measure]

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.

1 ACCEPTED 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().


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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().


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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