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
Anonymous
Not applicable

Dates Filters

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.

SyedAlam_0-1625022057109.png

 

 

 

 

7 REPLIES 7
amitchandak
Super User
Super User

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

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

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

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
Not applicable

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

 

 

 

@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
Not applicable

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.

SyedAlam_0-1625462494213.png

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

Thanks

Syed

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

vjaneygmsft_1-1625646759977.png

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.

Helpful resources

Announcements
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!