Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
So there are two images here. The second image shows roughly what I'm trying to create.
THe first image shows some of the struggles I'm having. I have a dataset that gives me actual orders by day. I've used a union query to drop on top of that the budget orders, but that's a separate issue right now.
I am struggling to get the rolling orders to separate by month. If you see the Power BI image below, what it's doing is summing everything in the whole "actual" dataset and not just in the relevant month. The monthly sum amounts should be growing to $60,000 - $100,000 or so, not millions. And the Jan and Feb line is put right on top of each other in this image. I do also have a "date" table with all the relevant dates (2019-2020) included. Any ideas? thanks.
@Zarlot531, You can create a MTD measure and plot it by month year. Use date Calendar and have you month year there.
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
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))))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. 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/
I've actually made some progress going down a slightly different path, but now I'd like to use an IF statement to stop the line, at, say, 30 days for months with 30 days in them. I realize this isn't the most elegant way to do this, but I for some reason I can't get the IF statement to see my "DayMonth" (days in the month) field. "DayMonth" is the number of days in the month. It's a column repeated with that value for each record.
Ok, let me try to clean this up a bit/make things easier. I've attached a link to the file.
I have managed to get the rolling part working, but I'd like to be able to stop it at the end of the month, the 28th or 29th for Feb, 30th or 31st for other months. I'd also like to stop it at the current day we're actually on, using some sort of getdate(). Below is the link, any help is appreciated. I guess also one other thing is I need the budget to be able to be selected as well, which I will try to play with some on my own tomorrow.
OK, so I figured out how to make the months stop at the right day. Only main issue now is I can't seem to get the "budget" line in at the same time as the actual line...like, I'd like to show the budget for March at the same time I'm showing the actual.
This is where I'm at now... The "month lag" tables simply have (or will have) 1-31, 1-29, 1-30, 1-28. But when I try to test with 29, the Feb line goes flat..hmmm
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |