Dates Filters

[Anonymous]

Hi All,

I am stuck to get the results based on the client requrements.

I have two dates columns from different table. exm 1 date column from table A which is Sales Date and 2nd date column from table B wheich is Date Recieved. if i gave the Date Recieved as a user filter when user selected date picker till 1st May 2021

then sales values shuld be calculated based on the Sales date up to 1st June 2021.

I have attached the screenshot for refrence.

[amitchandak]

@Anonymous , Based on what I got. You need an independent table.

Then in you measure

Measure   =

var _min = minx(allselected('Date'), 'Date'[Date]) 

var _Max= eomonth(today(), -1)

return 

calculate(sum(Sales[Value]), filter(Sales , Sales [date received] >= _min && Sales [date received]  <=_max))

[Anonymous]

Hi Amit,

Thanks for the quick responce. Yes you are right i need to show in one table.

here is I have created the calculation  and (Date Recvd) using for Filter (Slicer) and date picker selected till 1st June 2021

the result is not matching 

Previous Month test =

var _min = MINX(ALLSELECTED('Warranty'[Date Recvd]),'Warranty'[Date Recvd])

var _max = EOMONTH(TODAY(), -1)

RETURN

CALCULATE([Warrent cost/unit], FILTER(Sales,'Sales'[TranDate].[Date] >= _min && Sales[TranDate].[Date] <= _max))

[v-janeyg-msft]

Hi, @Anonymous 

Is [Warrent cost/unit] a measure? I don’t know your context, it’s hard to modify the code.

Can you share some sample data and your desired result? So we can help you soon.

Best Regards

Janey Guo

[Anonymous]

Hi Janey,

Yes [Warrent cost/unit] is a measure calculation.

Is it possible to have a remote support help to understand the problem.

Thank you...

[v-janeyg-msft]

@Anonymous 

For your information security, we don't support other ways except forum, we will do our best to help you solve the problem. And if you can provide .pbix file, we can help you more directly. Of course, real data and sensitive information should be deleted.

Best Regards

Janey Guo

[Anonymous]

Hi Janey,

Yeah, I have created the tables using sample data.

Requirement

There are two dates columns from different tables.

One is 'Order Date' and another one is 'Ship Date' 'Order Date' we are using

filter (Slicer) and another one 'Ship Date' need to calculate base on the user selection.

Ex. when user selected the date range from which is 'Order Date' (slicer) till 1st May

then

'Ship Date' need to calculated till 1st June.

https://www.dropbox.com/s/kujebed8o1a535j/test.pbix?dl=0 

Thanks

Syed

[v-janeyg-msft]

Hi, @Anonymous 

If I understand you correctly, I think you need to create a distinct table then use the date column as a slicer.

Like this:

Table = DISTINCT(Orders[Order Date])

Measure =
VAR a =
    EDATE ( MAX ( 'Table'[Order Date] ), 1 )
RETURN
    CALCULATE ( SUM ( Orders[Sales] ), FILTER ( Orders, [Order Date] <= a ) )

Measure2 =
CALCULATE (
    SUM ( Orders[Sales] ),
    FILTER ( Orders, [Order Date] <= MAX ( 'Table'[Order Date] ) )
)

If it doesn't solve your problem, Please feel free to ask me.

Best Regards

Janey Guo

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