Forum Discussion
Line graph with start and end date
Hi guys,
I know this is a widely discussed problem, and there are many solutions around on the PowerBI forums but I can't get any of them working for me somehow. Could anyone help me out?
I have a table of units, when they entered the warehouse and when they left the warehouse, like so:
| Unit | DateIn | DateOut |
| 1 | 1-1-2019 | 5-1-2019 |
| 2 | 4-1-2019 | 7-1-2019 |
| 3 | 5-1-2019 | 13-1-2019 |
I also have a date table with date, month, year, quarter, etc.
Now I want a line graph to show for every date how many units were in the warehouse for that date.
I tried the following solution, but it returns a very high number of units on any given date and I can't figure out why. It seems to work fine for the linked case and mine is basically identical.
UNIT Count =
VAR _FirstDate = FIRSTDATE ( Dates[Date] )
VAR _LastDate = LASTDATE ( Dates[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( Table[UNIT] ),
Table[DateIn] <= _LastDate,
Table[DateOut] > _FirstDate || ISBLANK ( Table[DateOut] )
)
Please help!
hi Anonymous
try to create new calculated table
Table UNIT counts = addcolumns(Dates;"UNIT Count";countrows(FILTER(ALL('Table');'Table'[DateIn]<=[Date] && 'Table'[DateOut]>=[Date])))or add a column to your Calendar table
countrows(FILTER(ALL('Table');'Table'[DateIn]<=[Date] && 'Table'[DateOut]>=[Date]))do not hesitate to give a kudo to useful posts and mark solutions as solution
1 Reply
- az38Community Champion
hi Anonymous
try to create new calculated table
Table UNIT counts = addcolumns(Dates;"UNIT Count";countrows(FILTER(ALL('Table');'Table'[DateIn]<=[Date] && 'Table'[DateOut]>=[Date])))or add a column to your Calendar table
countrows(FILTER(ALL('Table');'Table'[DateIn]<=[Date] && 'Table'[DateOut]>=[Date]))do not hesitate to give a kudo to useful posts and mark solutions as solution