Hello everyone!
Complete newcomer to Power BI here. I've only been introduced to it this week but I'm already really enjoying the customization and functionality that the software offers. Since I'm not super familiar with DAX or anything like that, I've made extensive use of some of the resources available on here, but I'm still having one problem that's persisting.
In my dataset I have a heavy amount of transaction data (several hundred thousand rows, at least), and I'm trying to find a way for us to compare our current revenue in June (which is incomplete) and compare it to an identical range in the previous month. I've already created a date table (date) in addition to the transaction data table (Query1).
Essentially, I want to compare June's MTD sales with what May's MTD sales would have been at the same time. So, for example, I could be comparing sales from June 1st to June 15, and I'd want to see how those numbers compare with ours from May 1 - May 15.
I've managed to write a function that let's me view current June numbers against all of May's, but I still am unable to compare the specified date ranges easily. I can manually do it in the filters, but I'm hoping there's an easier way to do it.
I've also tried running the in several different ways, and in every case I'm able to get May's total revenue, but not it's running total to compare with June's. Below are what I managed to put together for June's rolling total and May's total. The scripts were originally used to compare the last full month and the month prior to that one (Like May and April, for instance).
ThisMonth = CALCULATE(SUM(Query1[Amount]), FILTER(Query1, IF(OR(MONTH(TODAY())=11,month(TODAY())=12), Query1[Year]=YEAR(TODAY()), Query1[Year]=YEAR(TODAY()))), FILTER(Query1, Query1[Month]=MONTH(TODAY())))
For Last Month:
LastMonth = CALCULATE(SUM(Query1[Amount]), FILTER(Query1, IF(OR(MONTH(TODAY())-1=11,month(TODAY())-1=12),Query1[Year]=YEAR(TODAY())-1, Query1[Year]=YEAR(TODAY()))), FILTER(Query1, Query1[Month]=MONTH(TODAY())-1))
I've tried messing around with the DATEADD and PARALLELPERIOD functions, as well, but haven't been able to gain much traction with them. I feel like potentially incorporating a DATEADD component into the second of these two scripts could work, but I'm not sure.
Finally, I tweaked a script that I found that said it would give me the previous month's rolling total, but I ended up getting the same issue as above, where I got the full amount for May and not for a range that matches the amount of days that have elapsed in June. I've attached that script as well.
Previous Month Rolling Total = CALCULATE(SUM(Query1[Amount]), FILTER(ALL('Date'),('Date'[Monthnumber])=MAX('Date'[Monthnumber])-1 && 'Date'[Day] <=MAX('Date'[Day])))Let me know if I can clarify anything. Again, these scripts all run well, I'm just too inexperienced (I think) to figure out how to tweak them right now.
