none
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

    Please help me on that 

    Thanks in advance 


    Monday, February 03, 2014 11:15 AM

Answers

  • 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 ,

    Thank you for giving reply 

    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 )
    )

    Please help me  and do neeedful

    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

    Please help me on that 

    Please reply me soon


    Sreeni

    Tuesday, March 18, 2014 12:36 PM