Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Create diagram without month-lines

Hi,

 

I work with powerBI a few months and build some nice dashboards. When connecting to a new database/table I'm lost! I

I need to determine witch customer is a customer in a period and visualize this nice in a diagram per month. (example: https://www.dropbox.com/s/tircx3909iy00wl/PowerbiForum.png )   

 

I'm used to work with dataset with a filled row for every month, thats easy. But this case I never had before. I need an diagram for every year/month but how?

 

 Where to start? 

 

Dataset looks like:

CustomerNameStartdateEnddate
Piet1-1-202031-12-2020
klaar5-5-2020 
Jan2-2-20201-4-2020
Frits1-5-2019 
Tom1-4-201930-4-2020
Floor1-1-2020 
Bas1-1-202031-12-2020

 

 

 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

     

    Table:

     

    Calendar(a calculated table):

    Calendar = CALENDARAUTO()

     

    There is no relationship between two tables. You may create a measure as below.

    Count = 
var _max = MAX('Calendar'[Date])
var _min = MIN('Calendar'[Date])
var tab = 
SUMMARIZE(
    'Table',
    'Table'[CustomerName],
    'Table'[Startdate],
    'Table'[Enddate],
    "flag",
    var startdate = [Startdate]
    var enddate = [Enddate]
    return
    IF(
        NOT(ISBLANK(enddate)),
        IF(
            NOT(
                OR(
                    startdate>_max,
                    enddate<_min
                )
            ),
            1,0
        ),
        IF(
            startdate<=_max,
            1,0
        )
    )
)
return
SUMX(
    tab,
    [flag]
)

     

    Result:

     

    Best Regards

    Allan

     

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

     

3 Replies

  • collinq's avatar
    collinq
    Super User
    6 years ago

    Hi Anonymous ,

     

    You really should create a Date table so that you can have consistency on your dates and be able to use the visual with month data - even for months that are not in your dataset.  

     

    Then, link to the date table.

     

    Then, based on your dropbox picture, use the Clustered Column chart or perhaps the stacked column chart.

     

    I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!
  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

     

    Table:

     

    Calendar(a calculated table):

    Calendar = CALENDARAUTO()

     

    There is no relationship between two tables. You may create a measure as below.

    Count = 
var _max = MAX('Calendar'[Date])
var _min = MIN('Calendar'[Date])
var tab = 
SUMMARIZE(
    'Table',
    'Table'[CustomerName],
    'Table'[Startdate],
    'Table'[Enddate],
    "flag",
    var startdate = [Startdate]
    var enddate = [Enddate]
    return
    IF(
        NOT(ISBLANK(enddate)),
        IF(
            NOT(
                OR(
                    startdate>_max,
                    enddate<_min
                )
            ),
            1,0
        ),
        IF(
            startdate<=_max,
            1,0
        )
    )
)
return
SUMX(
    tab,
    [flag]
)

     

    Result:

     

    Best Regards

    Allan

     

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