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

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

Reply
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

xiaotianchi111_0-1592947738170.png

 

which is what I have so far. Please help

1 ACCEPTED SOLUTION

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

View solution in original post

7 REPLIES 7
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

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

Table:

d1.PNG

 

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:

d2.PNG

 

Best Regards

Allan

 

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

 

amitchandak
Super User
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

Thanks for your help!

parry2k
Super User
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

Thanks for your help!

 

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. 

@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!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.