cancel
Showing results for
Did you mean:
Member

## Need help filtering dates with a Period slicer !

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])))

6 REPLIES 6
Member

## Re: Need help filtering dates with a Period slicer !

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

Community Support Team

## Re: Need help filtering dates with a Period slicer !

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
Member

## Re: Need help filtering dates with a Period slicer !

@v-shex-msft

Hi unfortunately that just loops and doesnt provide a calulation.

Community Support Team

## Re: Need help filtering dates with a Period slicer !

HI @ballist1x,

I'd like some sample data for test.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
Member

## Re: Need help filtering dates with a Period slicer !

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
Community Support Team

## Re: Need help filtering dates with a Period slicer !

HI @ballist1x,

Maybe you can take a look at following link:

Slicers For Selecting Last "X" Periods

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

Announcements