Forum Discussion
Calculating items per month
First of all, you may find this information useful: https://radacad.com/do-you-need-a-date-dimension. You've already got a separate calendar, so you're ahead of the curve here!
So the reason this is breaking likely depends on your context. The way measures work is that they evaluate in the current context. Imagine you had a measure that does as you want and calculated the total divided by workdays that month. If you were looking at your entire table and asked it "How much?" It would give up evaluating the measure since it has no idea which [days in month] value you want to use for dividing.
But if you put it in a table with Month as a column, the table filters data by month for you into each row. In each table row you can evaluate the measure again by asking "How much where month is equal to January?" This still returns an error, because even though you've filtered the context down to a single month, it is looking at a table of all the days in January and doesn't know which [days in month] value to use. However, at this point, every [days in month] available is the same value, so you can use SELECTEDVALUE to return one value to use for your division.To fix the DAX measure, try this:
PPD = DIVIDE( SUM(BEX_Proc_Visits[Amount]), SELECTEDVALUE(CALENDAR[days in month]) )
You also don't need to use a CALCULATE here, because you aren't applying or changing the filters, which is when calculate is useful.
Just remember, when you're creating a measure, you usually need to use some sort of aggregation function to get one value out of each column that you're using. Usually this will be a mathematical aggregation like SUM or COUNT, but can also be SELECTEDVALUE or FIRSTNONBLANK depending on your situation.Yeah, your problem is the context.
So you were getting no result in your card because the card doesn't have a specific month associated with it. All I've done to your file is create context for the PPD measure when I display it.
https://drive.google.com/open?id=1A5j-yOAOi8lQTpNdrYrDmVgWAn3c8D1i
Note that if you select a month in the month slicer, it will give a value for PPD in the card. However, if you de-select the year while still having a month selected, it will return a blank again, because Jan 2019 has a different amount of [days in month] than Jan 2015 or Jan 2017. You have to set the PPD in a context where there's only one option for [days in month] available.You'll also see weird results if you select April, May, July, and August 2019, since they all have the same number of work days, you'll still get a PPD value. Same with Jan 2018 & Jan 2019. The measure isn't foolproof, but as long as you only display it in logical contexts, it will work.
First of all, you may find this information useful: https://radacad.com/do-you-need-a-date-dimension. You've already got a separate calendar, so you're ahead of the curve here!
So the reason this is breaking likely depends on your context. The way measures work is that they evaluate in the current context. Imagine you had a measure that does as you want and calculated the total divided by workdays that month. If you were looking at your entire table and asked it "How much?" It would give up evaluating the measure since it has no idea which [days in month] value you want to use for dividing.
But if you put it in a table with Month as a column, the table filters data by month for you into each row. In each table row you can evaluate the measure again by asking "How much where month is equal to January?" This still returns an error, because even though you've filtered the context down to a single month, it is looking at a table of all the days in January and doesn't know which [days in month] value to use. However, at this point, every [days in month] available is the same value, so you can use SELECTEDVALUE to return one value to use for your division.
To fix the DAX measure, try this:
PPD = DIVIDE( SUM(BEX_Proc_Visits[Amount]), SELECTEDVALUE(CALENDAR[days in month]) )
You also don't need to use a CALCULATE here, because you aren't applying or changing the filters, which is when calculate is useful.
Just remember, when you're creating a measure, you usually need to use some sort of aggregation function to get one value out of each column that you're using. Usually this will be a mathematical aggregation like SUM or COUNT, but can also be SELECTEDVALUE or FIRSTNONBLANK depending on your situation.
I probably should have clarified, there are many months of sales data on the fact table and different cuts of data so you have to sum it up to get total sales for the month.
If I was doing it in SQL it would be something like
declare @Currentmonth date
declare @Currentmonth2 date
declare @Currentmonth3 date
set @currentmonth = getdate()
sent @Currentmonth2 = dateadd(mm,-1,@currentmonth)
sent @Currentmonth3 = dateadd(mm,-2@currentmonth
Sum(Case when posting_month = @currentdate then amouth else 0) CurrentMonthSales
Sum(Case when posting_month = '@currentdate2 then amouth else 0) PriorMonthSales
.etc
I tried the formula you sent, but blank value was returned
A blank value was returned in what context? Where are you trying to display the result of this measure? In a card visual? A table or matrix?
That measure will evaluate to blank unless you have the context filtered down to only one distinct value in the [days in month] column. Context is key when it comes to measures. The measure above will sum up all the Amounts in BEX_Proc_Visits, and divide that by the total days in the month, but only in a context where one month has been selected.If you create a table visual that has year and month as values, and you drag+drop this measure into the values, it will display the value for each month.