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 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])
rpinxt's avatar
rpinxt
Icon for Solution Sage rankSolution Sage
3 years ago

Greg_Deckler amazing....spot on 😁👍

Code looks not straight forward...what would be the bottleneck in my model causing all these troubles (because with standard formulas it would not work)?

 

Also just for my knowledge could it also return the YTD amount?

Probably it is there somewhere in the logic.

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    rpinxt Should be to just use the same code and do a SUMX instead of an AVERAGEX.

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

      Thanks! Learned a lot todayy 😁