Showing data for all dates in a line graph

[gfournier]

Hello,

I have 4 tables:

1. Date
   
2. Projects
   
3. 'Expense codes'
   
4. Expenses
   

Here are the relationships:

Source file of where I am now:
https://www.dropbox.com/s/kzfyzjms3euxf5n/Not%20working%20example.pbix?dl=0

I have a matrix that shows the total amount of expenses per project and per expense code, and a line graph (that can be filtered using the matrix) that I'd like to show the running sum for the selected "Project+Expense code" for all dates in Dates (not just the dates where there are data points from the Expenses table).

Here is the Expenses[RunningAmount] DAX (being plotted on the line graph):

Here is how the line graph looks right now (unfiltered by the matrix):

Here is how the line graph looks right now (filtered using matrix):

However, as stated earlier, I want to show the running sum for the selected "Project+Expense code" for all dates in [Dates], not just the dates that have expenses.

Here is how I'd like the line graph to look like:

[EDIT] This section was confusing, I removed it

I understand this might be a little hard to understand so I have created another file that shows how I'd like the line chart to look like: https://www.dropbox.com/s/tgm7h34mas3c2kh/Proper%20line%20chart.pbix?dl=0

In this file, I had to create a merge table (Date+Expenses) to get the line chart I wanted.  However, this is not applicable for a lot of projects and a lot of expense codes.

More detailed info if needed (you can skip):

The dashboard has a matrix that shows the total amount of expenses per project, per expense code. There is also a line chart that shows the running expense amount in time. When I select a combination Project+Expense code from the matrix at the top, I'd like the line chart to show the running amount for those only. This seems to work fine. However, I'd like the line chart to show the progression of the expense for the whole range of dates in the Dates table, not only where there are data points. Let's say I select Project1+[Code1], the line chart shows a slanted line between May 7 2021 and September 4th 2021. I would like the line chart to show the whole range of dates (from May 1st 2021 to April 30th 2023) and expenses increasing per step when an expense is made.

Thanks for sticking with me til the end! Can you point me in the right direction ?

Guillaume

[parry2k · Accepted solution]

@gfournier this is my last try, you provided great details but over complicated everything, update your measure as this:

RunningAmount = CALCULATE(
    SUM(Expenses[ Amount ]),
    FILTER(
        ALLSELECTED('Date'[Date]),
        'Date'[Date] <= MAX('Date'[Date])
    )
) + 0

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@gfournier I have no idea what you are referring to, you should check the pbix file you gave and that has the table which is used in the measure and apply the same formula in your "original" file (i don't know which one is that) and just replace the table name in the measure. Not sure why it is confusing, most likely it is an expense table in your original file.

[gfournier]

@parry2k  I have modified my original post to strikethrough the text that might have confused you.  There were two files in my original post, you are not using the right one.

[parry2k]

@gfournier  this table was in your pbix file, I didn't create it.

[gfournier]

@parry2k  The file called "Proper line chart.pbix" was created only to show the line chart that I wanted to get so you could see my goal.  It does not use the table structure of the "Not working example.pbix" file that represent the structure of my real data.  So I basically want to get the line chart from the "Proper line chart.pbix" file by using only the "Not working example.pbix" file.  Sorry if that wasn't clear.

In brief, what should I do on the "Not working example.pbix" file to get the following line chart when I filter using the matrix (Project1+Code1):

[parry2k]

@gfournier use this measure :

RT = 
CALCULATE ( 
    SUM ( 'Expenses Project1 Code1'[ Amount ] ), 
    FILTER ( 
        ALL ( 'Date'[Date] ), 
        'Date'[Date] <= MAX( 'Date'[Date] ) 
    ) 
) + 0

and here is the output

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[gfournier]

@parry2k Thanks for your time, it's really appreciated.

When I try your solution, it does not find 'Expenses Project1 Code1'[ Amount ]:

How do you create this table? Does this mean a new table for each project and each expense code has to be created? This would not work for my data, I have way to much project and codes to do such a thing.