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 (
        [PROFIT M],        
        'Calendar'[Date] <= MaxDate, 
        ALL ('Calendar'[Date])           
    )

 

 

The results just matching the Profit by month, not cumulative.

 

Below is the table and Data view. 

 

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

9 Replies

  • johnt75's avatar
    johnt75
    Super 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.

         

  • divyed's avatar
    divyed
    Super User
    1 year ago

    Hello AivaS ,

     

    Your DAX formula for calculating the cumulative profit has a small issue.

    Modify your dax like below and try again :

     

    RT Profit =
    VAR MaxDate = MAX('Calendar'[Date])
    RETURN
    CALCULATE(
    [PROFIT M],
    FILTER(
    ALL('Calendar'),
    'Calendar'[Date] <= MaxDate
    )
    )

     

    I hope this helps.

     

    Cheers

    • AivaS's avatar
      AivaS
      Frequent Visitor
      1 year ago

      Unfortunately, it still has the same result. 

      • divyed's avatar
        divyed
        Super User
        1 year ago

        Hello AivaS ,

         

        I have tested on dummy data and it is working. I have created a measure total_Sales and I have da date table. Please replace your tables accordingly and try again

        Here is my dax :

        RunningTotalSales =
        VAR CurrentDate =
            MAX ( Datestbl[Date] )
        RETURN
            IF (
                NOT ( ISBLANK ( Measurestbl[TotalSales] ) ),
                CALCULATE ( Measurestbl[TotalSales], Datestbl[Date] <= CurrentDate ),
                BLANK ()
            )
         

         

        I hope this helps .

        Did I answer your query ? Mark this as solution if this solves your issue, kudos are appreciated.

         

        Cheers.

  • AivaS's avatar
    AivaS
    Frequent Visitor
    1 year ago

     

    I thinkI need to add a filter in the formula? it seems then I use the "Calendar" Table, I get this resulin the table ? 
    Sum of profit being a column, Profit M being a measure, RT profit is the one I amtrying to work out. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      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.