cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aJ2 Regular Visitor
Regular Visitor

Calculating the values for the complete month based on date slicer

Hi.

 

I want to calculate the target number based on the date slicer.

 

For example, the date range selected is 1st May 2018-23rd June 2018, then the output should be 1st May 2018-31st May 2018 + 1st June 2018-30th June 2018).

 

Likewise, if the date range selected is 1st March 2018-10th March 2018, the output should be the target of 1st March 2018-31st March 2018.

 

I have created a measure but it is only considering it for the current month-

 

revenue target = CALCULATE(SUM(data[targettarget]),MONTH('data'[date])=MONTH(TODAY()))

 

So when I change the date range to May, I can still see only the values for June.

 

 

Kindly guide.

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User
Super User

Re: Calculating the values for the complete month based on date slicer

try this

revenue target:=CALCULATE(SUM(data[targettarget]),PARALLELPERIOD(data[date],0,MONTH))
Community Support Team
Community Support Team

Re: Calculating the values for the complete month based on date slicer

Hi @aJ2,

 

You need a calendar table which is unrelated to data table, generateed by CALENDAR() function. 

 

revenue target =
CALCULATE (
    SUM ( data[targettarget] ),
    MONTH ( 'data'[date] ) >= MONTH ( MIN ( calendar[dates] ) )
        && MONTH ( 'data'[date] ) <= MONTH ( MAX ( calendar[dates] ) )
)

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Highlighted
Super User
Super User

Re: Calculating the values for the complete month based on date slicer

try this

revenue target:=CALCULATE(SUM(data[targettarget]),PARALLELPERIOD(data[date],0,MONTH))
Community Support Team
Community Support Team

Re: Calculating the values for the complete month based on date slicer

Hi @aJ2,

 

You need a calendar table which is unrelated to data table, generateed by CALENDAR() function. 

 

revenue target =
CALCULATE (
    SUM ( data[targettarget] ),
    MONTH ( 'data'[date] ) >= MONTH ( MIN ( calendar[dates] ) )
        && MONTH ( 'data'[date] ) <= MONTH ( MAX ( calendar[dates] ) )
)

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
aJ2 Regular Visitor
Regular Visitor

Re: Calculating the values for the complete month based on date slicer

Hi @v-yulgu-msft and @Stachu

 

Thank you so much...

 

Both the solutions worked for me