Need help filtering dates with a Period slicer !

[ballist1x]

Hi all, i am trying to calculate a last period revenue figure, i.e sum the value of the sales of accounts b

H1[Final Amount] is the revenue (calculation)

H1[ posting date] are the order dates

Last Invoice Date = CALCULATE(MAX(H1[Posting Date]), ALLEXCEPT('H1', 'H1'[Customer Number]))

Select Period = IF(countrows(Period)=1, MAX(Period[Period]),BLANK())

period table is a table with 30, 60, 90, 120, 365 etc

i want to sum all the the Final Amount (£) for the date between Today minus the selected period, i.e 30 so today - 30 days and return the summed final amount of the accounts if the Today - 30 (for example)

this is my calculation but it doesnt return the results that i want:(

Last Period Revenue = CALCULATE(sum(H1[Final Amount (£)]), DATESBETWEEN(H1[Posting Date], DATEADD(LASTDATE(H1[Today]), -1*[Select Period], DAY), LASTDATE(H1[last invoice date])))

[ballist1x]

if anyone can help to come up with a solution that would be great!

[v-shex-msft]

Hi @ballist1x,

Maybe you can try to use below measure if it works for your requirement:

Last Period Revenue =
VAR currentDate =
    MAX ( H1[Posting Date] )
RETURN
    CALCULATE (
        SUM ( H1[Final Amount (£)] ),
        FILTER (
            ALLSELECTED ( H1 ),
            Hi[Posting Date]
                >= currentDate - MIN ( [Select Period], 0 )
                && Hi[Posting Date] <= LASTDATE ( VALUES ( H1[last invoice date] ) )
        )
    )

If not help, please share sample or pbix file with expected results.

Regards,

Xiaoxin Sheng

[ballist1x]

@v-shex-msft

Hi unfortunately that just loops and doesnt provide a calulation.

[v-shex-msft]

HI @ballist1x,

I'd like some sample data for test.

Regards,

Xiaoxin Sheng

[ballist1x]

Hi @v-shex-msft

for example

YEAR_ENDING

PERIOD

POSTING_DATE

NAV_NO_CODE

NAV_NO_DESCRIPTION

Customer Number

Customer

DOCUMENT_NO

QUANTITY

Revenue

CUSTOMER_NAME

MANUFACTURER

Revenue Group

Hierarchy Level 3

2018

1

01/01/2018

NX-11111

Heinz

ACC155

INVOICE123

1

20

Newci

Heinze

CONDIMENTS

TEA

2018

1

01/03/2018

KTCH1

Ketch

ACC155

INVOICE125

1

15

DALE

Heinze

CONDIMENTS

LUNCH

2018

1

01/01/2018

SLDCRM1

SALAD

ACC156

INVOICE126

6

200

BOROM

WORCESTER

CONDIMENTS

LUNCH

[v-shex-msft]

HI @ballist1x,

Maybe you can take a look at following link:

Slicers For Selecting Last "X" Periods

Regards,

Xiaoxin Sheng