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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
matteob
Frequent Visitor

manage table with entry date and exit date

Hi, I have a table with a list of material with a unique code with the relative purchase cost.

For each row there is an entry date and an exit date. I would like to be able to view a list with the material present on a specific date.

I have set up a calendar table with no relationships. I added a calculated column to the table with the material in order to filter the list later. This is what I put in the calculated column, but it doesn't work. What's wrong?

 

column =
var seldata = SELECTEDVALUE(calendario[Date])
var datacons = Query1[Consegna]
var dataarr = Query1[Carico]
return
IF((dataarr<seldata && datacons>=seldata),1,0)


thanks

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

@matteob 

Create a measure to identify the rows in which the date_in is before the minimum date selected and the out_date is after the maximun date selected using:

 

Code In Stock = 
VAR InDate = MIN(calendario[Date])
Var OutDate = MAX(calendario[Date])
RETURN 
COUNTROWS(
    CALCULATETABLE(
            VALUES(Foglio1[code]),
            FILTER(Foglio1,
            Foglio1[date_in] <= InDate &&
            Foglio1[date_out] > OutDate)))

 

Add this measure to the filter on the visual in the filter pane and set the value to 1.

Result.JPG

 

I've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

10 REPLIES 10
PaulDBrown
Community Champion
Community Champion

@matteob 

Create a measure to identify the rows in which the date_in is before the minimum date selected and the out_date is after the maximun date selected using:

 

Code In Stock = 
VAR InDate = MIN(calendario[Date])
Var OutDate = MAX(calendario[Date])
RETURN 
COUNTROWS(
    CALCULATETABLE(
            VALUES(Foglio1[code]),
            FILTER(Foglio1,
            Foglio1[date_in] <= InDate &&
            Foglio1[date_out] > OutDate)))

 

Add this measure to the filter on the visual in the filter pane and set the value to 1.

Result.JPG

 

I've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






v-janeyg-msft
Community Support
Community Support

Hi, @matteob 

 

You need to know that the calculated column is calculated when the data is loaded. It won't change with your choice. To filter the data, you need to change the calculated column to measure, and then put it in the filterpane.

measure =
VAR seldata =
    SELECTEDVALUE ( calendario[Date] )
VAR datacons = SELECTEDVALUEQuery1[Consegna]
VAR dataarr = SELECTEDVALUEQuery1[Carico]
RETURN
    IF ( ( dataarr < seldata && datacons >= seldata ), 1, 0 )

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.

Thanks @v-janeyg-msft,
I tried with the measure as you said, but unfortunately the table view is not filtered.

@matteob ,Have you put it in filter pane?

Like this:

5.png

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.

 

@v-janeyg-msft, yes, I tried,

but it doesn't work, the measure gives me the table values ​​only with 0

 

 

Immagine.png

Hi, @matteob 

 

It's difficult to see the problem with only a formula. Can you share same sample data? So we can help you soon.

 

Best Regards

Janey Guo

 

Hi, @v-janeyg-msft

this is the sample file.

https://www.dropbox.com/s/29wbqudkcaknuhm/date_in_out_test.pbix?dl=0 

 

Thank you very much for your help.

Matteo

matteob
Frequent Visitor

Is there another way to view the table filtered on these dates ?

amitchandak
Super User
Super User

@matteob , You can not create a column with the selected value. Means you can not use slicer value in measure, you have to use it in a  measure

Ok thanks for the reply.

Yes, in fact, in a "measure" I managed to make the sum of the costs with these parameters.

 

Is there another way to view the table filtered on these dates?

I should also see the list of material present on the date entered.

 

 

 

 

 

 

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors