cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## Power BI - Date DAX in measure.

I would like to know the Productive Hours by special date period.

For example, if today is 6/23/2020 so I want the data from 6/16/2010 to 6/23/2020

if today is 6/13/2020 so I  want the data from 6/1/2020 to 6/13/2020.

Basically the 15 of this month will be a period. if today is 6/15/2020 so I want the data from 6/1/2020 to 6/15/2020. I have put a screen shot below

1 ACCEPTED SOLUTION
Super User

@Anonymous i think this is what you need:

``````Measure =
VAR __maxDate = MAX ( DateTable[Date] )
VAR __startDay = IF ( DAY ( __maxDate ) <= 15, 1, 16 )
VAR __startDate = DATE ( YEAR ( __maxDate ), MONTH ( __maxDate ), __startDay )
RETURN
CALCULATE ( <<your measures>>, DATESTBETWEEN ( DateTable[Date], __startDate, __maxDate ) )``````

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

7 REPLIES 7
Community Support

Hi, @Anonymous

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Calendar(a calculated table):

``Calendar = CALENDARAUTO()``

There is a relationship between two tables. You may create a measure as below.

``````Result =
var _date = SELECTEDVALUE('Calendar'[Date])
var _startdate = EOMONTH(_date,-1)+1
return
IF(
DAY(_date)<=15,
CALCULATE(SUM('Table'[Productive Hrs]),DATESBETWEEN('Calendar'[Date],_startdate,_date)),
CALCULATE(SUM('Table'[Productive Hrs]),DATESBETWEEN('Calendar'[Date],DATE(YEAR(_date),MONTH(_date),16),_date))
)``````

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User

@Anonymous , Try like

``````measure =
var _max = today()
var _min = if(day(_max)<=15 ,eomonth(_max,-1)+1,date(year(_max),month(_max),16))
return
calculate(sum(Table[Data]), filter(all(Date),Date[Date]>=_min && Date[Date]<=_max))``````
Anonymous
Not applicable

Super User

@Anonymous you don't need to put filter statement

``CALCULATE ( [Your measure], DATESBETWEEN ( DateDimension[Date], StartDate, EndDate ) )``

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

The problem I'm meeting is I do not sure how to varaible a Enddate.

For example, mostrecent day is

VAR maxdate = MAX(YZ_TB_IMPORTS_DATALOT_INVOICING[Date])

and first day of this month is
VAR startdate = DATE(YEAR(maxdate),MONTH(maxdate),"1")

how to variable the 16 of this month since if today is 6/24/2020, so I need the date from 6/16/2020 to 6/24/2020.
Super User

@Anonymous i think this is what you need:

``````Measure =
VAR __maxDate = MAX ( DateTable[Date] )
VAR __startDay = IF ( DAY ( __maxDate ) <= 15, 1, 16 )
VAR __startDate = DATE ( YEAR ( __maxDate ), MONTH ( __maxDate ), __startDay )
RETURN
CALCULATE ( <<your measures>>, DATESTBETWEEN ( DateTable[Date], __startDate, __maxDate ) )``````

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Thank you so much and it works!