Running Total for the same month of last year

[diogoricciardi]

Hey guys,

I have a Sales table that has the sales amount of a company since Jan/2018.

I'm building a line chart where I want to show three lines with the following running totals:
- Month (August/20)

- Month -1 (July/20)
- Month in Year -1 (August/19)

To calculate the running total in the current month I used :

CALCULATE([Sales Amount],

                   FILTER(

                              ALLSELECTED(Sales_Table),

                                                    Sales_Table[Sales_Date] <= MAX(Sales_Table[Sales_Date])

                    ),

                              MONTH(Sales_Table[Sales_Date]) = MONTH(TODAY()),

                              YEAR(Sales_Table[Sales_Date]) = YEAR(TODAY())

)

And it worked perfectly.

Now, for the other periods, I've already tried this in a lot of different ways, always getting the same results, sometimes a flat line with the total amount of the respective month (when using all selected), other times daily sales amount but not accumulated ( when not using allselected).

Can you guys give me a light of how I can do this?

Thanks in advance,

Diogo

[Fowmy · Accepted solution]

@diogoricciardi 

If you need to have the three measures as you need, first create a calendar table and link it to the sales table, add the measures as given in the attached file.

You can download the file: HERE

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[diogoricciardi]

Hey @Fowmy ,

Thank you very much for your reply.

I had managed to do it by creating a column in the sales table where the max date was the last day of the month I wanted to get but your solution is far more ellegant.

Thanks for the lesson.

Cheers

[harshnathani]

Hi @diogoricciardi ,

You need to have a date calendar and mark it as the date in model view.
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/

and you can use these for MTD Calculation

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last to last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-2,Year)))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))

Regards,

Harsh Nathani

[diogoricciardi]

Hi harshnathani,

Thank you for your reply.

I'm sorry, I think I wasn't very clear.

What I mean by running total is that it accumulates given a certain period, let me give you an example:

Day	Sales Amount	Running Total (What I need)
1	10	10
2	10	20
3	10	30

I'm having trouble calculating the running totals for periods last month and same period of last year.

[harshnathani]

Hi @diogoricciardi ,

You can use this measure

Measure =
CALCULATE (
    SUM ( Table[Sales amount] ),
    FILTER (
        ALL ( Table ),
        Table[Day]
            <= MIN ( Table[Num] )
    )
)

https://www.daxpatterns.com/cumulative-total/

https://community.powerbi.com/t5/Desktop/Calculating-running-total-based-on-month/td-p/426337

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)