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

Greg_Deckler Anonymous 
Thanks guys for your help.
However no luck:

Greg his formula still refuses to add up periods.

And the measure of dax_noob is doing somehting strange.

Changing DESC to ASC did not help it.

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

rpinxt Post sample source data as text please. It's just pure guesswork otherwise. This video may help though: https://youtu.be/meh3OkgFYfc

 

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

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

    Greg_Deckler I understand Greg. Made a simpler new model for myself to check the logics but there they work.....

     

    It is a rather complicated (for me) model but I put in on Google Drive for you to have a look at.

    Saw that that was also a possibility to share my file with you so here is the link:

    https://drive.google.com/file/d/1vQAq2eifdjwul0bE6Un7OPQmfDvPpKd2/view?usp=sharing

     

    And what I try to achieve is actually what is in the green column below :

     

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