Create measure with calculation based on Date selected in Slicer

[Anonymous]

Hi together,

I am new to Power BI and struggling with the following problem:

I want to create a measure which tells me the average amount of cartons picked per labour day based on the selected month in the slicer, right now I am dividing the month selected with 20 labour days:

cartonbymonth = SUM('table'[Totalcartonamount])/20

My problem is when I pick two,three month, a year etc.. the data is still devide by 20. Which results in a false average per Day.

Is there a possibility to detect how many month are selected in the slicer and for each month that is selected devide with 20 days more.

Any solutions on how to fix it? Thank you

[OdetaJ · Accepted solution]

Hi @Anonymous,

Since you didn't show the desired result, I'm guessing that you wanted something like this:

For example, I selected 3 months, and for the last measure, I used the formula (created a measure):

Avg. Sales by Selected Months = DIVIDE( [Total Sales], 20 * [# Selected Months] )

Where another measure is:

# Selected Months = DISTINCTCOUNT( 'Date'[Month] )

Here, 'Date'[Month] is month numbers as shown in the slicer above.

In your case, measure [Total Sales] would be [Total Carton Amount] = SUM( 'table'[Totalcartonamount] )

Of course, I would suggest automatically calculating labour days in selected months to have accurate numbers.

[Anonymous]

Thank you it worked out !

[MAwwad]

Hello

Try this > 

Measure =

Var ValueSelected = COUNTROWS(VALUES(DIM_Date[Date].[Month]))

Return IF(ValueSelected = 1 , SUM('table'[Totalcartonamount])/20, SUM('table'[Totalcartonamount])/20 * ValueSelected

Dont forget to do the necessary changes

Please dont forget to accept this as a solution if it helped 🙂