Forum Discussion

Simao_Coimbra's avatar
Simao_Coimbra
Frequent Visitor
6 years ago
Solved

Simple Cumulative Total with a measure

Hello  everyone, I need some help that I think will be quite simple, but I'm not getting it. I have a measure called "HF_Mes", that returns the average chilling hours per month. HF_Mes ...
Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi Simao_Coimbra ,

     

    Please refer to my sample.

    I have a table like this.

    Then I created a calculated column.

    // It's best to use [MonthNo], because [Month] is text type data, we can't sort it. 
Month = [Date].[MonthNo]

     Then I created a meaure.

    Cum = 
CALCULATE(
    [HF_Mes],
    FILTER(
        ALLSELECTED(Sheet1),
        Sheet1[Month] <= MAX(Sheet1[Month])
    )
)

     

    Best regards,
    Lionel Chen

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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Simao_Coimbra , Try with a date calendar 

Cumm = CALCULATE([HF_MES],filter(date,date[date] <=maxx(date,date[date])))
Cumm = CALCULATE([HF_MES],filter(date,date[date] <=max(TABLE[Date])))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

  • Simao_Coimbra's avatar
    Simao_Coimbra
    Frequent Visitor
    6 years ago

    Hi amitchandak .

    I already tried your purposed solution and it didn't work. I get the same result of the first column and not the accumulate.

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      6 years ago

      Simao_Coimbra ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

      • Simao_Coimbra's avatar
        Simao_Coimbra
        Frequent Visitor
        6 years ago

        Thank you for your patience
        I have a lot of data but in short, this is it:

        With this, I would like to have the average of the sum of chilling hours per month from October to April, so I used the first measure and would like to see a graph with these accumulated values.

        Once again thank you very much

         

         

        DateChilling Hours
        02/01/19856.87235759717314
        03/01/19855.05843347046371
        17/02/19850
        18/02/19852.59814757763975
        14/03/19856.4112
        15/03/19851.55136513288706
        16/03/19854.74383499934762
        17/03/19853.91264422938577
        18/03/19853.47041640814479
        19/03/19850
        20/03/19850
        21/03/19850
        14/03/19856.4112
        15/03/19851.55136513288706
        16/03/19854.74383499934762
        17/03/19853.91264422938577
        18/03/19853.47041640814479
        19/03/19850
        20/03/19850
        21/03/19850
        28/05/19850
        29/05/19850
        30/05/19850
        31/05/19850
        01/06/19850
        02/06/19850
        03/06/19850
        25/07/19850
        26/07/19850
        27/07/19850
        28/07/19850
        29/07/19850
        30/07/19850
        31/07/19850
        01/08/19850
        02/08/19850
        03/08/19850
        04/08/19850
        05/08/19850
        06/08/19850
        07/08/19850
        08/08/19850
        09/08/19850
        10/08/19850
        26/08/19850
        27/08/19850
        28/08/19850
        29/08/19850
        30/08/19850
        31/08/19850
        01/09/19850
        02/09/19850
        03/09/19850
        04/09/19850
        05/09/19850
        06/09/19850
        07/09/19850
        08/09/19850
        26/10/19850
        27/10/19850
        28/10/19853.83983833057834
        29/10/19850
        30/10/19850
        31/10/19850
        14/11/19850
        15/11/19853.64104139941691
        21/12/19855.38914614003591
        22/12/19850
        23/12/19850
        24/12/19850
        27/01/19960
        28/01/19960
        29/01/19960
        30/01/19960
        31/01/19960
        01/02/19960
        02/02/19960
        03/02/19960
        04/02/19964.61236428571429
        05/02/19960
        06/02/19960
        07/02/19960
        08/02/19960.664657268722467
        09/02/19960
        10/02/19962.56567463333822
        17/04/19960
        18/04/19961.63002184115196
        19/04/19960
        20/04/19960
        27/05/19960
        28/05/19960
        29/05/19960
        30/05/19960
        31/05/19960
        01/06/19960
        02/06/19960
        03/06/19960
        04/06/19960
        05/06/19960
        06/06/19960
        07/06/19960
        08/06/19960
        09/06/19960
        10/06/19960
        11/06/19960
        12/06/19960
        13/06/19960
        14/06/19960
        15/06/19960
        16/06/19960