Forum Discussion

AivaS's avatar
AivaS
Frequent Visitor
1 year ago
Solved

Cumulative Formula by date

Dear All, I am been trying to workout why the Cumulative cost is not working, please see my forumal below.       RT Profit = VAR MaxDate = MAX ( 'Calendar'[Date] )  RETURN     CALCULATE ( ...
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi, AivaS 
     Thank you for your prompt response.

    1.You can try the following measure: 

    Measure = 
VAR table1 =
    SUMMARIZE(
        ALLSELECTED('summary_by_month'),
        'summary_by_month'[Date].[Month],
        "AVG",
        VAR cm = 'summary_by_month'[Date].[Month]
        VAR _averageProfit =
            CALCULATE(
                AVERAGE(summary_by_month[profit]),
                FILTER(
                    ALL(summary_by_month),
                    'summary_by_month'[Date].[Month] = cm
                )
            )
        RETURN _averageProfit,
        "INEDX1",
        SWITCH(
            'summary_by_month'[Date].[Month],
            "January", 1,
            "February", 2,
            "March", 3,
            "April", 4,
            "May", 5,
            "June", 6,
            "July", 7,
            "August", 8,
            "September", 9,
            "October", 10,
            "November", 11,
            "December", 12
        )
    )
 
VAR table2 =
    SUMMARIZE(
        table1,
        'summary_by_month'[Date].[Month],
        [INEDX1],
        "running",
        SUMX(
            FILTER(table1, [INEDX1] <= EARLIER([INEDX1])),
            [AVG]
        )
    )
 
VAR f =
    SUMX(
        FILTER(table1, [INEDX1] = MAX([INEDX1])),
        [AVG]
    )
 
VAR f1 =
    SUMX(
        FILTER(table2, 'summary_by_month'[Date].[Month] = MAX('summary_by_month'[Date].[Month])),
        [running]
    )
 
RETURN
    IF(
        ISINSCOPE('summary_by_month'[Date].[Month]),
        f1,
        f
    )

    2.Here's my final result, which I hope meets your requirements.

    Please find the attached pbix relevant to the case.

     

    Best Regards,

    Leroy Lu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

johnt75's avatar
johnt75
Icon for Super User rankSuper User
1 year ago

Your RT Profit is a calculated column. Delete the column and use the same code to make a measure.

  • AivaS's avatar
    AivaS
    Frequent Visitor
    1 year ago

    RT profit is a measure, 
    maybe the issue is that in RT profit measure I am using Profit M, measure which is created by the following formula :

    PROFIT M = AVERAGE('summary_by_month'[profit])
     
    since the "profit" is a column and I needed a measure to calculate. 
    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Thanks for the reply from divyed and johnt75 , please allow me to provide another insight:

      Hi, AivaS 
      Thanks for reaching out to the Microsoft fabric community forum.

       

      The most efficient solution currently is to use visual object calculations:

      1.Here is my test data:

      Initial visualization results:

      2.Click on the visual object and select visual calculation:

      3.Here's my final result, which I hope meets your requirements.

      4.For further details, please refer to:

      Using visual calculations in Power BI Desktop - Power BI | Microsoft Learn

       

      Please find the attached pbix relevant to the case.

       

      Best Regards,

      Leroy Lu

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

       

      • AivaS's avatar
        AivaS
        Frequent Visitor
        1 year ago

        Excellent, thank you so much. That was very easy, indeed, however, I will need to use the measure of Running Total in KPI cards, so from testing out it seems the visual calculation only works for that particular visual, and does not create a measure?