Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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:

UnitDateInDateOut
11-1-20195-1-2019
24-1-20197-1-2019
35-1-201913-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.

https://community.powerbi.com/t5/Desktop/Make-line-graph-with-starting-date-and-end-date/m-p/804322#M386864

 

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

    LinkedIn

     

1 Reply

  • az38's avatar
    az38
    Community 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

    LinkedIn