# i would greatly appreciate any input for a DAX solution to my problem

### Question

• i have 2 years customer expense data for that i calculated MTD ,QTD ,YTD , Last year MTD,QTD ,YTD but

when i go to my power view report , i have added 2 filters one for Month and Year

My requirement is

suppose QTD(Apr,May,June) of this year having 15000

if i apply filter on month of Apr then it will show 5000(example)

if i apply filter on month of may then it will show 10000

if i apply filter on month of June then it will show 15000

but i want total value i.e 15000 of that Quarter where ever apply filter i.e, starting month of quarter(Apr) or end of

quarter(June)

for the same filter year column also either it Jan or Dec it has to show total value for Year with out filter based on Month

Monday, February 03, 2014 11:15 AM

• You might use PARALLELPERIOD. For example, you can get the total quarter value in this way:

=CALCULATE(
[measure],
PARALLELPERIOD ( DateTime[DateKey], 0, QUARTER )
)

Marco Russo
http://www.sqlbi.com
http://www.powerpivotworkshop.com

Monday, February 03, 2014 4:05 PM

### All replies

• You might use PARALLELPERIOD. For example, you can get the total quarter value in this way:

=CALCULATE(
[measure],
PARALLELPERIOD ( DateTime[DateKey], 0, QUARTER )
)

Marco Russo
http://www.sqlbi.com
http://www.powerpivotworkshop.com

Monday, February 03, 2014 4:05 PM
• Hi Marco Russo ,

The formula looks fine and it is showing the total Quarter value if i put filter on month it is not filtered thats fine to me.

=CALCULATE(
SUM([measure]),
PARALLELPERIOD ( DateTime[DateKey], 0, QUARTER )
)

I have used same formula above

But ,

it is working for Current Year Quarter  i need for the same for the  Previous Year Quarter

for that i used below formula but it is not working

=CALCULATE(
SUM([measure]),
PARALLELPERIOD ( DateTime[DateKey], -1, QUARTER )
)

thanks,

Sreeni

Tuesday, February 11, 2014 7:51 AM
• Have you tried with this?

= CALCULATE (
SUM ( [measure] ),
PARALLELPERIOD (
SAMEPERIODLASTYEAR ( DateTime[DateKey] ),
0,
QUARTER
)
)

Marco Russo http://www.sqlbi.com http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo

Tuesday, February 11, 2014 9:15 AM
• Hi Marco Russo ,

= CALCULATE (
SUM ( [measure] ),
PARALLELPERIOD (
SAMEPERIODLASTYEAR ( DateTime[DateKey] ),
0,
QUARTER
)
)

I have used the above DAX function it is working fine and i have applied same for Year , Quarter and Month

But when i remove month filters (Slicer) and Year filters in Power view report

it is showing total Year values for Year and Quarter values for Quarter etc..

Like below i am getting

﻿Last Yr Month Amt   Current Yr Month Amt   Last Yr Qtr Amt   Current Yr Qtr  Amt    Last Yr     Current Yr

﻿ 10000                     30000                              10000                30000                     30000       30000

but i need Blanks in report if i am not selecting any filters

thanks,

Sreeni

Tuesday, February 11, 2014 2:08 PM
• You can use IF ( HASONEVALUE ( DateTime[Month] ), .... ) so that if there is no selection then a BLANK is returned.

Marco Russo http://ssasworkshop.com http://www.sqlbi.com http://sqlblog.com/blogs/marco_russo

Tuesday, February 11, 2014 2:18 PM
• Hi Marco Russo ,

Last Year Amt:=CALCULATE(SUM(PPR_data[Amt]),

PARALLELPERIOD(SAMEPERIODLASTYEAR(DimTime[Date]),0,Year))   this is my expression

where should i add IF Condition in this expression

help me.

Tuesday, February 11, 2014 3:42 PM
• This way:

Last Year Amt:=
IF (
HASONEVALUE ( DateTime[Month] ),
CALCULATE(SUM(PPR_data[Amt]),
PARALLELPERIOD(SAMEPERIODLASTYEAR(DimTime[Date]),0,Year))
)Please consider that you might have to write a more complex IF statement depending on your requirements - I suggest you to study DAX syntax, otherwise you will continue to need help to write any formula! :)

Marco

Marco Russo http://ssasworkshop.com http://www.sqlbi.com http://sqlblog.com/blogs/marco_russo

Tuesday, February 11, 2014 4:08 PM
• Hi

Help me,

I Have to calculate for 1 Year rolling values from current date

example let say i am in march-2014   i have to rolling my column values for 1 year means April-2013

For this what function should i use

and also one more thing I have a string values in my column like

Status

Active

Seperated

Active

Seperated

Resigned

Resigned

Abscond

Abscond

I need to apply 1 year rolling formula on this column

how should i get these total count  for 1 year rolling