calculate sales within period

[ngocnguyen]

Hi, I am calculating sales speed within a month and get stuck with it.

I have 2 table : 1 name " Sales_ Amt" and other name " Cut_off"

Belows is the logics for calculate sales speed:

Sales  amt By 10th day = Total Sales _Amt from the 1st day to 10th day in DayID2 colume

Sales  amt By 20th day = Total Sales_Amt from 1st day to 20th day in Day ID2 colume

Sales  amt  Last 3days = Total Sales_Amt that  Cut-off date  <= Bill date <= Last billing date

So, How can I calculate it?

Thanks in advance for your support!

[v-alq-msft · Accepted solution]

Hi, @ngocnguyen 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Test:

There is no relationship between two tables. You may create measures as below.

Sales  amt By 10th day = 
var tab = 
ADDCOLUMNS(
    'Test',
    "Result",
    var _monthid = [Month ID]
    return
    CALCULATE(
        SUM('Table'[Actual Sales Amount]),
        FILTER(
            ALL('Table'),
            'Table'[Month ID] = _monthid&&
            DAY('Table'[DayID2])>=1&&
            DAY('Table'[DayID2])<=10
        )
    )
)
return
SUMX(
    tab,
    [Result]
)

Sales  amt By 20th day = 
var tab = 
ADDCOLUMNS(
    'Test',
    "Result",
    var _monthid = [Month ID]
    return
    CALCULATE(
        SUM('Table'[Actual Sales Amount]),
        FILTER(
            ALL('Table'),
            'Table'[Month ID] = _monthid&&
            DAY('Table'[DayID2])>=1&&
            DAY('Table'[DayID2])<=20
        )
    )
)
return
SUMX(
    tab,
    [Result]
)

Sales  amt Last 3days = 
var tab = 
ADDCOLUMNS(
    'Test',
    "Result",
    var _id = [Month ID]
    var t = 
    ADDCOLUMNS(
        ALL('Table'),
        "Flag",
        var _monthid = [Month ID]
        var _billdate = [Bill Date]
        var _cutoffdate = LOOKUPVALUE(Test[Cut-off],Test[Month ID],_monthid)
        var _lastbillingdate = LOOKUPVALUE(Test[Last billing date],Test[Month ID],_monthid)
        return
        IF(
            _cutoffdate<=_billdate&&_billdate<=_lastbillingdate,
            1,0
        )
    )
    return
    SUMX(
        FILTER(
            t,
            [Month ID]=_id&&
            [Flag]=1
        ),
        [Actual Sales Amount]
    )
)
return
SUMX(
    tab,
    [Result]
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ngocnguyen]

@v-alq-msft 

Thanks so much. You make my day !

[amitchandak]

@ngocnguyen , I am not sure what you want call these segments so-called them class 1, 2, ...

Create it as a new column in the date column

and join it with the date of first table

switch( true(),
Day(Date) <= 10 , "!= Class 1",
Day(Date) <= 20 , "!= Class 2",
countx(filter(Table2, format(Date[Date],"YYYYMM") =Table2[Month ID] && Table2[Cut-off date] <= Date[Date]
&& Date[Date] <= Table2[Last billing date]),Table2[Month ID] ) >0 , "Class3",
"Other")

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[ngocnguyen]

@amitchandak 

Thanks for your support. Howerver, Class 2 will cover Class 1 because:

-Class 1: from 1st day to 10th day

-Class 2: from 1 st day to 20 day

=> How to create a column as you suggested?