Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
selpaqm
Helper V
Helper V

DATESINPERIOD and Filter not working together

 Hi All,

 

I have a table as below and 1 date table which have already connected to each other.

Datecustomerqty
01/01/2021A2
01/01/2021B1
01/11/2020A1
01/02/2021A4
01/02/2021B3
01/10/2020B7
01/12/2020A5
01/12/2020B3
below measure is working correctly to sum last 3 months qtys with selected month in slicer.
3 month qty =
CALCULATE(SUM(sales[qty]),DATESINPERIOD('Date'[Date],LASTDATE('Date'[Date]),-3,MONTH))
 
However, I want to see specificly customer A figures and tried to change my measure as below and not working only shows me last month figures not for 3 months.
3 month qty for A =
CALCULATE(SUM(sales[qty]),FILTER(sales,sales(customer)="A"),DATESINPERIOD('Date'[Date],LASTDATE('Date'[Date]),-3,MONTH))
below link you may see an example for it
1 ACCEPTED 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 )
)

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

View solution in original post

6 REPLIES 6
selimovd
Super User
Super User

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

 

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

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

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

@selimovd thnks for the answer. it is working like charm.

amitchandak
Super User
Super User

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@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. 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.