Table with Date from earlierst till latest already given Date

[lynnsop]

Hello.

What I have:

Table1

Name Date
Richard 12.07.2021

Stephen 12.07.2021

Miller 18.07.2021

Table2

Simon 15.07.2021

Samy 30.07.2021

Dong 30.07.2021

What I need:

A line chart starting with the earliest date of those two tables, ending with the latest. Between those dates it should have a constant x axis. Meaning every day between those should also be shown (with value 0 obviously). 

(Line for example goes up on 12th and 30th -> 2).

Do you have any suggestion on how to realize this? Maybe a new table with all dates between earliest and latest date as a base table to use?

Thanks in advance

[selimovd]

Hey @lynnsop ,

you can and also should do that with a date table.

Check the following tutorial how to create a date table:

https://softcrylic.com/blogs/power-bi-for-beginners-how-to-create-a-date-table-in-power-bi/

In the data model you connect the date table with your other tables by date. In all the charts and other visuals you will only use the date from the date table. The dates in the other tables are just keys to connect to your date table.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[lynnsop]

Hi @selimovd 

This helps already! Thank you?

Can I though somehow automatically calculate the date ranges so they are not static as in the tutorial?
Basically something like:
Date = Calendar( EarliestDateFromBothTables, LatestDateFromBothTables)

[selimovd]

Hey @lynnsop ,

yes, that's absolutely possible.

Try the following approach:

Date =
CALENDAR(
    MIN( Table1[Date], Table2[Date] ),
    MAX( Table1[Date], Table2[Date] )
)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[lynnsop]

Hi @selimovd 

Thank you for your time.

Unfortunately this DAX doesnt seem to work. PowerBI doesnt expect a new column in second field of MIN and MAX. 

[selimovd · Accepted solution]

Hey @lynnsop ,

sorry, my mistake. In this case you should compare 2 scalar values.

So each value has to use the MIN or MAX function. The following should work:

Date =
CALENDAR(
    MIN( MIN( Table1[Date] ), MIN( Table2[Date] ) ),
    MAX( MAX( Table1[Date] ), MAX( Table2[Date] ) )
)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[lynnsop]

@selimovd 

Ah yeah, simple as that. Thank you very much! 🙂

I need help with another topic which I didnt find a solution to yet. Maybe you could have a look at this too? This would help me a lot

https://community.powerbi.com/t5/Desktop/New-table-from-two-tables-with-condition/m-p/1967608#M74556...