Forum Discussion

rpinxt's avatar
rpinxt
Icon for Solution Sage rankSolution Sage
3 years ago
Solved

3 different formulas with same outcome....why?

I have these measures : Prod_ACT = [Actual] / [FTE_ACT]   Prod_Ytd_ACT = CALCULATE([Prod_ACT],DATESYTD(Dim_Date[Date]),VALUES(Dim_Date[Period]))   Prod_Ytd_ACT2 = TOTALYTD([Prod_ACT],Dim_Date[Da...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    rpinxt Try this:

    Measure = 
    VAR __Date = MAX(Dim_Date[Date])
    VAR __Year = MAX('Dim_Date'[Year])
    VAR __Table = 
        SUMMARIZE(
            FILTER(
                ALL('Dim_Date'),
                [Date] <= __Date && [Year] = __Year
            ),
            [Period],
            "__Prod_ACT",[Prod_ACT]
        )
RETURN
    AVERAGEX(__Table,[__Prod_ACT])
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

rpinxt Eschewing a long diatribe about CALCULATE and TI functions, try this:

Measure = 
  VAR __Period = MAX('Table'[Period])
  VAR __Table = ADDCOLUMNS(FILTER('Table',[Period] <= __Period),"__Prod_ACT",[Prod_ACT])
RETURN
  SUMX(__Table,[__Prod_ACT])