DATESINPERIOD and Filter not working together

[selpaqm]

 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

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

trial

[selimovd]

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

Blog: WhatTheFact.bi

[selpaqm]

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

[selimovd · 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

Blog: WhatTheFact.bi

[selpaqm]

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

[amitchandak]

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

[selpaqm]

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