Sales last 6 month ignoring date filter context in table

[MIkkelHyldig]

Hi 

I want to see the sales of an item for the last 6 month ignoring the "last purchased date" in my table which seems to filter out my sales if the last purchased date is more than 6 month ago.

Currently, Im using the following measure but it gives a 0 for item purchased more than 6 month ago: 
Sales 6 month = CALCULATE (SUM ( 'Value Entry'[Invoiced Quantity] ),'Value Entry'[Item Ledger Entry Type] = 0,

DATESINPERIOD('Calender'[Date],TODAY(),-6,MONTH),ALL(Calender[Date]))

The result I get with the measure with todays date (1st. of feb 2022 for the example) is: 

Item No	sales 6 month	Last purchased date
1	0 (missing due to last purchased date filter?)	30.07.2021
2	0 (missing due to last purchased date filter?)	30.07.2021
3	0 (missing due to last purchased date filter?)	30.07.2021
4	7.600	01.08.2021
5	10.200	01.08.2021

Best regards,
Mikkel

[v-kkf-msft]

Hi @MIkkelHyldig ,

I could not fully understand, are you expecting to see sales for the last 6 months?

And how do you want to handle the data before 6 months?

Is your calendar table associated with the [Last purchased date] column?

Best Regards,
Winniz

[MIkkelHyldig]

Hi @v-kkf-msft ,

I could not fully understand, are you expecting to see sales for the last 6 months? Yes. Eg. if I purchased an item 1 year ago there can/will still be sales within the last 6 month 

And how do you want to handle the data before 6 months? Lets see if I can explain it. I want the two measures (last purchaed date and sales 6 month) to ignore each other for the items so that item 1 still have sales for the last 6 months evne though the last purchased date is more than 6 month ago

Is your calendar table associated with the [Last purchased date] column? No not directly.  Calender is key to posting data in Value Entries table, which is related by Item key to Value Entries, where last purchased date is. 
My data model:

Table 1: Value Entries
Posting data
Item (key to table 2)
Quantity

Table 2: Value Entries New
Item (key to table 1)
Last Purchased date

Table 3: Calender
Date (key to posting date)

[v-kkf-msft]

Hi @MIkkelHyldig ,

Since your [Last purchased date] column is not associated with the Calendar table, the [Last purchased date] will not affect the final result. I create the following example data and relationship.

           

Then using your formula, it returns the correct value. So please check if you have any missing data or any relationship that makes the Calendar table indirectly filter the [Last purchased date].

Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@MIkkelHyldig , Check one of the two

Sales 6 month = CALCULATE (SUM ( 'Value Entry'[Invoiced Quantity] ),filter('Value Entry','Value Entry'[Item Ledger Entry Type] = 0),
DATESINPERIOD('Calender'[Date],TODAY(),-6,MONTH))

or

Sales 6 month = CALCULATE (SUM ( 'Value Entry'[Invoiced Quantity] ),filter('Value Entry','Value Entry'[Item Ledger Entry Type] = 0),
filter(,ALL(Calender[Date]), 'Calender'[Date] >= Eomonth(TODAY(),-6) && 'Calender'[Date] <= Eomonth(TODAY(),0)))