Forum Discussion

LX's avatar
LX
Frequent Visitor
9 years ago
Solved

YTD Running Total

Hi, I'm trying to create a YTD running total with sample data like below in Power BI. As you can see, there's value for each month, but since now is July, I only need the running total up to July. What DAX steps should I follow to get a measure like this?

 

Raw data is at daily level like column A. 

 

Appreciate if anyone can help. I have tried the running total solutions shared in the community; somehow none of them work in this case. 

 

 

LX

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    LX,

    Please create the following measures in your table and check if you get expected result.

    Cumulativ2017 = CALCULATE (
                       sum(Table1[Sales]),
                                              FILTER (
                                                      ALLSELECTED(Table1),
                                                           Table1[Order Date]<= MAX (Table1[Order Date])&& MONTH(MAX(Table1[Order Date]))<=MONTH(TODAY())),Table1[Years]=2017
                                       
                                  )
    Cumulativ = CALCULATE (
                       sum(Table1[Sales]),
                                              FILTER (
                                                      ALLSELECTED(Table1),
                                                      Table1[Years] = MAX (Table1[Years] ) &&
                                                             Table1[Order Date] <= MAX ( Table1[Order Date])
                                       ))
    Sum of sales2 = IF(VALUES(Table1[Years])=2017,[Cumulativ2017],[Cumulativ])



    Regards,
    Lydia

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    LX,

    Please create the following measures in your table and check if you get expected result.

    Cumulativ2017 = CALCULATE (
                       sum(Table1[Sales]),
                                              FILTER (
                                                      ALLSELECTED(Table1),
                                                           Table1[Order Date]<= MAX (Table1[Order Date])&& MONTH(MAX(Table1[Order Date]))<=MONTH(TODAY())),Table1[Years]=2017
                                       
                                  )
    Cumulativ = CALCULATE (
                       sum(Table1[Sales]),
                                              FILTER (
                                                      ALLSELECTED(Table1),
                                                      Table1[Years] = MAX (Table1[Years] ) &&
                                                             Table1[Order Date] <= MAX ( Table1[Order Date])
                                       ))
    Sum of sales2 = IF(VALUES(Table1[Years])=2017,[Cumulativ2017],[Cumulativ])



    Regards,
    Lydia

    • LX's avatar
      LX
      Frequent Visitor
      9 years ago

      Hi Anonymous,

       

      It worked ! Thank you!

       

      LX

  • fhill's avatar
    fhill
    Resident Rockstar
    9 years ago

     

    Depending on whether you need YTD (as of Today) or YTD (as of current month) I have two sample below that I think look like your provided data.

     

    YTD Sales_Today = CALCULATE(SUM(Table2[Sales]), DATESBETWEEN(Table2[Date],STARTOFYEAR(Table2[Date]), TODAY()))

     

    YTD Sales_Month = CALCULATE(SUM(Table2[Sales]), DATESBETWEEN(Table2[Date],STARTOFYEAR(Table2[Date]), EOMONTH(Today(),0)))

     

    Raw data to the left, summary table to the right:

     

     

     

    • fhill's avatar
      fhill
      Resident Rockstar
      9 years ago

      I just noticed you have 2016 data in your raw table as well as 2017.  You may need to add a LASTDATE(...) logic to the StartOfYear peice... FOrrest