Forum Discussion

MarcW's avatar
MarcW
Frequent Visitor
3 years ago
Solved

Userrelationship / ParrallelPeriod wrong subtotals

Hi all,

 

I have a problem with wrong subtotals in my calculations using Userrelationship / ParrallelPeriod.

The fact table contains cost information per employee and two date columns, date and source date (= snapshot date of data extraction).

 

Situation:

 

For my Solution I use this measure

 

MaxSourcedate =

VAR CY = 0    

VAR Source =
    CALCULATE(
         MAX( WorkDay[SourceDate] ),  
            USERELATIONSHIP( WorkDay[SourceDate], 'Date'[Date] ) ,
            PARALLELPERIOD('Date'[Date], CY, YEAR)    
        )                                              


VAR Result =
    CALCULATE(
        [Annual Pay],
        FILTER(
            ALL(WorkDay[SourceDate]),
                WorkDay[SourceDate] = Source
    )
)

RETURN

Result

 

For the PY:

 

MaxSourcedate PY =

VAR PY = -1    

VAR SourcePY =
    CALCULATE(
         MAX( WorkDay[SourceDate] ),  
            USERELATIONSHIP( WorkDay[SourceDate], 'Date'[Date] ) ,
            PARALLELPERIOD('Date'[Date], PY, YEAR)
        )


VAR Result =
    CALCULATE(
        [Annual Pay],
        DATEADD('Date'[Date], PY, YEAR),
        FILTER(
            ALL(WorkDay[SourceDate]),
                WorkDay[SourceDate] = SourcePY
    )
)

RETURN

Result

 

Problem:

 

 

I am not sure what I do wrong. My best guess is that my filter argument with

FILTER(
            ALL(WorkDay[SourceDate]),
                WorkDay[SourceDate] = SourcePY
gives back all values of the last sourcedate but then is not summing up in the totals. Has anyone an idea to fix that? Thanks in advance. 🙂

 

 

 

 

 

  • MarcW's avatar
    MarcW
    3 years ago

    Thanks for getting back to me. Unfortunately that was not working for me. In the meantime I did get help in another forum. My code missed a REMOVEFILTERS condition. Then it works fine:

     

    MaxSourcedate =

    VAR CY = 0

    VAR Source =
    CALCULATE(
         MAX( WorkDay[SourceDate] ),
         USERELATIONSHIP( WorkDay[SourceDate], 'Date'[Date] ) ,
         PARALLELPERIOD('Date'[Date], CY, YEAR) ,

         REMOVEFILTERS ( 'WorkDay' )
    )


    VAR Result =
    CALCULATE(
         [Annual Pay],
              FILTER(
              ALL(WorkDay[SourceDate]),
              WorkDay[SourceDate] = Source
         )
    )

    RETURN

    Result

     

     

    Thanks a lot anyway.

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    MarcW , Try like

     

    Var _measure = CALCULATE(
    [Annual Pay] ,USERELATIONSHIP( WorkDay[SourceDate], 'Date'[Date] ) )

    VAR Result =
    CALCULATE(
    _measure,
    Dateadd('Date'[Date], -1, year)
    )

     

     

    Have measure with userelationship and then use time intelligence

     

    Check the last period formula in my Hr file, attached after signature 

    • MarcW's avatar
      MarcW
      Frequent Visitor
      3 years ago

      Thanks for getting back to me. Unfortunately that was not working for me. In the meantime I did get help in another forum. My code missed a REMOVEFILTERS condition. Then it works fine:

       

      MaxSourcedate =

      VAR CY = 0

      VAR Source =
      CALCULATE(
           MAX( WorkDay[SourceDate] ),
           USERELATIONSHIP( WorkDay[SourceDate], 'Date'[Date] ) ,
           PARALLELPERIOD('Date'[Date], CY, YEAR) ,

           REMOVEFILTERS ( 'WorkDay' )
      )


      VAR Result =
      CALCULATE(
           [Annual Pay],
                FILTER(
                ALL(WorkDay[SourceDate]),
                WorkDay[SourceDate] = Source
           )
      )

      RETURN

      Result

       

       

      Thanks a lot anyway.