Help with Line Chart

[gbarr12345]

Hi,

I'm trying to create a line chart to compare the number of Volume create against the numbers of volume sold to customers (Depletion in my sample file) for 2 products - Pinot Grigio and Premium Red Wine.

I created relationships and they seem to be ok with one to many's.

However when I drag the data in the USA 9LE or volume seems ok but the Depletion 9LE is all the same number for Pinot Grigio and showing nothing for Premium Red Wine even though there should be data - Sample data in my screenshot below:

I want to create an accurate line chart to compare the US 9LE against the Depletion 9LE - example below but dark blue line needs to be different too:

Can anybody help me in achieving this? I've attached the sample PBI file below and also the Excel file I created to make this graph.

PBI File - https://drive.google.com/file/d/1xa9DqZ6Ck1YSook6a4zWVDa8yYCQjKFE/view?usp=drive_link

Excel - https://docs.google.com/spreadsheets/d/1A80g-0NjgtS4dgVcwLmglWIyrfH4eoVK/edit?usp=sharing&ouid=11647...

Any help would be greatly appreciated.

[adudani]

hi @gbarr12345 ,

Attached is my solution and an image of the output. Let me know if this meets the requirements.

steps taken:

1. added a date table.

2. Create 1 to many relationships from dimensions to facts.

3. Used the date from the date table instead of the depleation/usa table.

[gbarr12345]

Thank you for your response.

How did you create the date table do you mind me asking?

[adudani]

hi @gbarr12345 ,

references below have open source code.

using dax, you could create a new table and copy paste the dax code. Walk throughs of the code is in the videos.

Here is how to make it via DAX: Calendar Table using DAX (youtube.com) 

Using a blank query, copy paste the code into the advanced editor in power query:

Power BI Ultimate Calendar Table 🗓️ / Date Table 📆 [ FREE DOWNLOAD ] (youtube.com)

[gbarr12345]

Thank you for your response.

I have this code for a Date column but I want it to start from the year 2020 and end in the year 2025. Is there a way to altewr my formula to do this do you know?

ADDCOLUMNS(

    CALENDARAUTO(),

    "Dates" , FORMAT([Date], "DD/MM/YYYY"),

    "Year", YEAR([Date]),

    "Month" , FORMAT([Date] , "mmmm"),

    "Quarter" , FORMAT([Date] , "\QQ"),

    "Month Year", FORMAT([Date], "mmm yyyy"),

    "Day" , DAY([Date]),

    "Month Number" , MONTH([Date])

)

[adudani · Accepted solution]

hi @gbarr12345 ,

this link has the code with variable start and end date: 

avixd/CalendarTable_with_DAX: Code samples of a calendar table using DAX (github.com) 

Change what is highlighted in yellow for the output:

[gbarr12345]

Perfect thanks, that worked!

One more question, I copied your relationships, etc and for some reason the numbers for the depletion are still the same instead of giving me the correct number:

Any idea as to why that's happening?

[adudani]

is the "Date" in the row context from "DimDate" ?
Also, is DimDate marked as the Date table?

[adudani]

Alternatively, 

with the "DimDate" i provided in the sample output previously.

you could also modify the start and end date as shown below in the advanced editor