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 so...
  • 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.

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.