PowerBI/DAX ProgressToDate Calculation

0
votes

I am trying to create a calculated column (or measure if possible) that will allow me to see the difference between last months total sales and the current months total sales.

Right now I have a bar chart that shows the sales for every month (MonthName on the x-axis), but when I look at the current month, the sales are much lower because we're only 10 days into the month. I want a measure or calculated column that can tell me how much Sales I need in order to hit the amount of Sales I had last month.

Here is what I've come up with so far... (it doesn't work)

ProgressRemaining = CALCULATE(CALCULATETABLE('Date', 'Date'[MonthOfYear]>=MONTH(now()-1))), COUNT(LineItems[Quantity]), PREVIOUSMONTH('Date'[FullDate])) - CALCULATE( COUNT(LineItems[Quantity]), MONTH(NOW()))))
1
powerbidax

1 Answers

0
votes

I recommend a few different measures, which build on top of each other to accomplish what you'd like to accomplish.

  1. A measure for current revenue. I assume you have this already, and it is called Revenue. (The green in your above screenshot.)
  2. A measure for prior month revenue
  3. A measure to tell if it's the "current" month or not
  4. A measure to subtract current month revenue from prior month revenue, only if it is the current month

For #2: 

PriorMonthRevenue = CALCULATE([Revenue], DATEADD('Date'[FullDate], -1, MONTH))

For #3:

IsCurrentMonth = IF(CALCULATE(MIN('Date'[FullDate])) <= TODAY() && CALCULATE(MAX('Date'[FullDate])) >= TODAY(), TRUE, FALSE)

Note that there are probably better ways of doing this, especially if your data lags (i.e. if, on the 1st of the month, you still want the measure to behave like it is the previous month

For #4:

DifferenceFromLastMonth = IF([IsCurrentMonth] = TRUE, [PriorMonthRevenue]-[Revenue], 0)

Drag this final measure onto your chart as a Value and you should see January 2017 reflect an additional column that reaches to the height of December 2016.

Hope this gets you closer to where you want to be.