topic Re: Running Total for the same month of last year in DAX Commands and Tips

Running Total for the same month of last year

diogoricciardi — Sat, 15 Aug 2020 20:36:24 GMT

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

Re: Running Total for the same month of last year

Anonymous — Sat, 15 Aug 2020 21:12:50 GMT

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

Re: Running Total for the same month of last year

diogoricciardi — Sun, 16 Aug 2020 12:38:46 GMT

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.

Re: Running Total for the same month of last year

Anonymous — Sun, 16 Aug 2020 14:34:41 GMT

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)

Re: Running Total for the same month of last year

Fowmy — Sun, 16 Aug 2020 14:26:39 GMT

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

Re: Running Total for the same month of last year

diogoricciardi — Sun, 16 Aug 2020 14:40:30 GMT

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

Re: Running Total for the same month of last year

DataMoreData — Tue, 06 Aug 2024 12:42:16 GMT

Do you still have the file? The link no longer works