Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 :
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
Solved! Go to 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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
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
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.
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)
User | Count |
---|---|
53 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
92 | |
86 | |
39 | |
23 | |
22 |