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

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