Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi All,
I have a table as below and 1 date table which have already connected to each other.
Date | customer | qty |
01/01/2021 | A | 2 |
01/01/2021 | B | 1 |
01/11/2020 | A | 1 |
01/02/2021 | A | 4 |
01/02/2021 | B | 3 |
01/10/2020 | B | 7 |
01/12/2020 | A | 5 |
01/12/2020 | B | 3 |
Solved! Go to Solution.
Hello @selpaqm ,
the file helped a lot.
The way you use it you don't need the filter function, you can just override the filter context for the customer. Like this you get the correct value for every day.
Check that formula:
3 month qty for A =
CALCULATE(
SUM( sales[qty] ),
sales[customer] = "A",
DATESINPERIOD( 'Date'[Date], MAX( 'Date'[Date] ), -3, MONTH )
)
Hey @selpaqm ,
I'm wondering the measure works, you would have to use square brackets in the filter function "sales[customer]":
3 month qty for A =
CALCULATE(
SUM( sales[qty] ),
FILTER( sales, sales[customer] = "A" ),
DATESINPERIOD( 'Date'[Date], LASTDATE( 'Date'[Date] ), -3, MONTH )
)
I personally would prefer the DATESBETWEEN and DATEADD combination:
3 month qty for A =
VAR MaxDate = MAXX( ALLSELECTED( 'Date' ), 'Date'[Date] )
RETURN
CALCULATE(
SUM( sales[qty] ),
FILTER( sales, sales[customer] = "A" ),
DATESBETWEEN( 'Date'[Date], DATEADD( MaxDate, -3, MONTH ), MaxDate )
)
@selimovd thanks for your response. sorry my mistake while I was changing the names I have changed brackets into incorrect ones. however in my original one it is used correctly.
on your second measure it gives error due to dateadd needs a specific column.
by the way I have uploaded an example to original post. It may help.
Hello @selpaqm ,
the file helped a lot.
The way you use it you don't need the filter function, you can just override the filter context for the customer. Like this you get the correct value for every day.
Check that formula:
3 month qty for A =
CALCULATE(
SUM( sales[qty] ),
sales[customer] = "A",
DATESINPERIOD( 'Date'[Date], MAX( 'Date'[Date] ), -3, MONTH )
)
@selpaqm , Try with small change and check. Also, make sure the Date table is marked as Date Table(Right-click on table there is an option).
Rolling 3 = CALCULATE(SUM(sales[qty]),FILTER(sales,sales(customer)="A"),DATESINPERIOD('Date'[Date ],MAX('Date'[Date]),-3,MONTH))
@amitchandak thanks for quick reply. MAX is not worked for it. given same result with LASTDATE. date table is converted to date table as you mentioned.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
125 | |
78 | |
76 | |
59 | |
51 |
User | Count |
---|---|
166 | |
84 | |
68 | |
67 | |
57 |