Sum of the quantity in the SKU level for last 30 days

[Anonymous]

Hi all,

I would like to sum the quantity in the SKU level for last 30 days from today(), for each sales channel. 

The data table (example) looks like that:

I tried with this measure, but still, this is not done. Can anyone help me out with how to get the right result?

QuantityLast30Day =
CALCULATE (
SUM ( flatFileOrders[quantity]),
DATESINPERIOD (flatFileOrders[updatedAt], MAX(flatFileOrders[updatedAt]), -30, DAY )
)

I tried to see this link: Calculate Sales Past 30 Days, but it is not fulfilling the requirements.

Regards,

Ahsan

[Samarth_18]

Hi @Anonymous ,

Use your date table inside dateperiod function.

QuantityLast30Day =
CALCULATE (
SUM ( flatFileOrders[quantity]),
DATESINPERIOD (Datetable[date], MAX(Datetable[date]), -30, DAY )
)

Thanks,

Samarth

[Anonymous]

Hi @Samarth_18 , 

i don't have a separate date table in my data model. 

Do, i need to create a date table? if yes, can you please tell me how to do it?

I'm not very experienced in Power BI.

Thanks.

[Samarth_18 · Accepted solution]

@Anonymous if you dont have calender table with below code: based on requirement-

calender("01/01/2020","01/01/2025")

or else you can try below code:-

QuantityLast30Day =
VAR max_date =
    MAX ( flatFileOrders[updateat] )
VAR last_30days =
    MAX ( flatFileOrders[updateat] ) - 30
RETURN
    CALCULATE (
        SUM ( flatFileOrders[quantity] ),
        FILTER (
            flatFileOrders,
            flatFileOrders[updateat] <= max_date
                && flatFileOrders[updateat] >= last_30days
        )
    )