Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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])))
if anyone can help to come up with a solution that would be great!
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
HI @ballist1x,
I'd like some sample data for test.
Regards,
Xiaoxin Sheng
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 |
HI @ballist1x,
Maybe you can take a look at following link:
Slicers For Selecting Last "X" Periods
Regards,
Xiaoxin Sheng
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |