Monthly sales trend in Power BI based on working days

0
votes

I have daily sales data and I would like to create a measure or a column that calculates the monthly sales trend based on the average daily units sold in the month.

For example, as of 08/17/2017, we have had 12 working days this month and 360 units sold. This is an average of 30 units per day. So to calculate the trend manually, I would take 30 units/day and multiply it by the 23 total working days in the month for a trend of 690 units sold.

Here is some sample data.

enter image description here

2
powerbidax
Can you provide any sample data and example(s) of what your desired outcome is/are? How to create a Minimal, Complete, and Verifiable example – Joe Gravelyn
So the "Desired calculations" show exactly how you want to display the results in your report? I.e. just four measures with a month filter? – dybzon

2 Answers

0
votes

EDIT: Adjusted the explanation and measure definitions based on the new info that was added to the post

You can create three measures to help you reaching your final result. In this example I'm merely splitting the measure into three parts to increase the readability. You can put it all together in a single measure if you like.

The first measure gives you the number of units sold. This is just a simple sum of units sold, since you have already added a month filter to your measures in your sample:

Sales:=SUM('SalesTable'[Items Sold])

The second measure gives you the number of working days for the filtered month up until the latest entry in your Sales Table:

WorkingDaysToDate:=
CALCULATE(
    COUNTROWS('DateTable'), 
    FILTER(
        'DateTable', 
        'DateTable'[Date] > CALCULATE(MAX('SalesTable'[Date]))
        && [Type] = 'Work Day'
    )
)

The third measure gives you the total number of working days for the filtered month:

WorkingDays:=
CALCULATE(
    COUNTROWS('DateTable'),
    FILTER(
        'DateTable',
        [Type] = 'Work Day'
    )
)

In the end you can combine the three measures to get your month trend:

MonthTrend:=DIVIDE([Sales], [WorkingDaysToDate]) * [WorkingDays]
0
votes

Total Month To Date Sales

TotalMTDSales = SUM('Sales Table'[Items Sold])

Total Month To Date Work Days

TotalMTDWorkDays =  CALCULATE(
COUNT('DateTable'[Date]), 
FILTER(ALLSELECTED('DateTable'), 'DateTable'[Date]<=MAX('SalesTable'[Date]) && 
'DateTable'[Type] = "Work Day"))

Total Work Days

TotalWorkDays = CALCULATE(
COUNTROWS('DateTable'),
FILTER(
    'DateTable',
   'DateTable'[Type]= "Work Day"))

Trend = ([TotalMTDSales] / [TotalMTDWorkDays] ) * [TotalWorkDays]

Filter by Month