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.
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
which is what I have so far. Please help
Solved! Go to 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.
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.
@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))
Thanks for your help!
@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.
Thanks for your help!
The problem I'm meeting is I do not sure how to varaible a Enddate.
For example, mostrecent day is
@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.
Thank you so much and it works!
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 |
---|---|
106 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |