Calculating items per month

[Anonymous]

Hi all,

If there is a level below beginner that's me. I have a small data model built and connected. Of the tables, my fact table has month, year, items sold, item types .etc (many rows must be summed to get January total). My calendar table is by day and has total work days in month for each day as well as an ascending count of work day .  ( 1/8/19 would be the 5th workday but the row contains 22 for the january total) There is 5 years of calendar data.

How would you divide the sum of the items sold by the total workdays in month?

The error I'm getting is a "A single value for column Days in Month cannot be determined" 

Here's my formula:

PPD = calculate(divide(sum(BEX_Proc_Visits[Amount]),CALENDAR[days in month]))

Thanks in advance,

[Cmcmahan · Accepted solution]

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.

[v-cherch-msft]

Hi @Anonymous 

Could you share your sample data which could reproduce your scenario and your desired output so that we could help further on it?

Regards,

[Anonymous]

Here's the PBIX

Trying to calculate amount / Bdays per month

[Cmcmahan · Accepted solution]

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.

[Cmcmahan · Accepted solution]

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.

[Anonymous]

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

[Cmcmahan]

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.