Power BI - Date DAX in measure.

[Anonymous]

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

[v-alq-msft]

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.

[amitchandak]

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

Thanks for your help!

[parry2k]

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

[Anonymous]

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. 

[parry2k · 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.⚡

[Anonymous]

Thank you so much and it works!