Extend Line chart to end of x-axis on null or 0 values

[JustDavid]

Given the columnar file, I've specified that for [Attribute] that are either '5YP Cost' or 'Beg Balance/PYE Forward', I've put the date as May 1, 20xx, and 'Net Posting 01' as Jun 1, 20xx etc.

From the above, I was able to attain the my desired chart (blue line chart) where it's the running total (screenshot at the bottom).

I'm trying to plot the '5YP Cost' chart in the same blue line chart. However, as you can see, ASSUMING if I'm able to put it in the chart, it'll only have 1 value which is May 1, 2023 on the x-axis (denoted red square).

What I'd like to accomplished here is to have that plot across the x-axis.

Below are the Measures that I've tried but never able to get it to plot nor summing the correct amount.

cumulative5YP = 
    VAR _Date = MAX(tblDate[Calendar Date])
    VAR _tbl = FILTER(ALLSELECTED('CombineProformaPF-and-5YP'), 'CombineProformaPF-and-5YP'[Date] <= _Date)
RETURN
    SWITCH(TRUE(),
    AND(ISINSCOPE(tblDCHierarchy[Lv1_ShowDetail]), ISBLANK(VALUES(tblDCHierarchy[Lv1_ShowDetail]))) || 
    AND(ISINSCOPE(tblDCHierarchy[Lv2_ShowDetail]), ISBLANK(VALUES(tblDCHierarchy[Lv2_ShowDetail]))) || 
    AND(ISINSCOPE(tblDCHierarchy[Lv3_ShowDetail]), ISBLANK(VALUES(tblDCHierarchy[Lv3_ShowDetail]))) || 
    AND(ISINSCOPE(tblDCHierarchy[Lv4_ShowDetail]), ISBLANK(VALUES(tblDCHierarchy[Lv4_ShowDetail]))), BLANK(),
    CALCULATE([SumValue], _tbl, 'CombineProformaPF-and-5YP'[Attributes] = "5YP Cost")
    )

The closest that I can come up with (https://stackoverflow.com/questions/72816152/power-bi-create-continuous-line-chart-at-end-of-positio...) is via this google search where I was able to plot in each x-axis. However, it's giving me 0 values from beginning to end of x-axis

What is it that I'm missing?

Below is the screenshot that I desired to achieve

https://1drv.ms/u/c/c5e5e5ada9360f76/EeVC5F-1VAVIlh7gAdx0MNABXETCQxxm9xSCAClO9IX5pg?e=KanM2b <-- Link to my pbix

[sevenhills]

Hi,

My reply is only related to the static black line:

* Check, for the selected ones, the cost code column has data and cost code (raw) column has no data. You are linking the relationships based on cost code (raw) column

Just to get the things moving, I added a dummy column and used this in the relationships. This is NOT needed if you have the data in your relationship joining column.

Join Col Testing = COALESCE('CombineProformaPF-and-5YP'[Cost Code (RAW)], 'CombineProformaPF-and-5YP'[Cost Code])

* I tried to see how can we get 5000 and could not understand, based on the data values. So, hard coded the 5000 values here

5 YP Cost = 
    VAR _Date = MAX(tblDate[Calendar Date])
    VAR _tbl = FILTER(ALLSELECTED('CombineProformaPF-and-5YP'), 'CombineProformaPF-and-5YP'[Date] <= _Date && 'CombineProformaPF-and-5YP'[Attributes] = "5YP Cost" 
                                )
RETURN 
    SWITCH(TRUE(), 
        ISBLANK([cumulativePostings] ), BLANK(), 
        -- CALCULATE([SumValue], _tbl)
        CALCULATE(5000, _tbl) -- TBD of the formula to get 5000 here
    )

 Sample output:

[JustDavid]

@sevenhills Thank you for your help here.

To answer your questions, the reason why my relationship is based on the [Cost Code (RAW)] is because my data comes from different sources and that end user would like to see the detail cost code. And in order to make the category hierarchy to work, I clean up the [Cost Code (RAW)] taking only the 1st five digits of the cost code.

As to 5000, when I took the print screen, I had 'Permit' hierarchy selected (under General hierarchy).

I shall try and mimic your solution and hopefully able to produce the desired result.

I have two questions. In your reply which I quote "... I added a dummy column and used this in the relationships. This is NOT needed if you have the data in your relationship joining column." As you've seen that my relationship is based on Cost Code (RAW), do I need to add this dummy column? And what is this dummy column need to do?

Another question is regarding the measure (I assume it is)

Join Col Testing = COALESCE('CombineProformaPF-and-5YP'[Cost Code (RAW)], 'CombineProformaPF-and-5YP'[Cost Code])

Do I need this AFTER creating a dummy column?

Thanks

[sevenhills · Accepted solution]

[Cost Code (RAW)] is used in the relationships and your filters are based on other table and as we all know when there is no data, the filters wont propogate from tblDCHierarchy to the transaction table.

Based on the data, I saw the data can be in Cost Code and [Cost Code (RAW)] , so added the dummy column and used in the relationships. Just made a guess to solve the measure.

The dummy column is making sure has some data i.e., when [Cost Code (RAW)]  is not having data, use the Cost Code column data. Now, the relationship works (and filters works)! Hope this clarifies!

Do we need this dummy column?

If you can replace the empty data in [Cost Code (RAW)] column with [Cost Code] column data while bringing the data itself (in Power Query "CombineProformaPF-and-5YP"), then you dont need this additional column and your existing relationships works. No further changes! Purely, the decision of you need the new column or not is based on your data.  The main point is you need data so that relationships works (and filters works)!

Coming back to the measure, I think 5000 is not possible with the selected filters. 3080 is the sum of two values based on the filters and you can get static line 3080, which is NOT the answer you want. You can see this one gives 3080 for your scenario 🙂

5 YP Cost = 
    VAR _Date = MAX(tblDate[Calendar Date])
    VAR _tbl = FILTER(ALLSELECTED('CombineProformaPF-and-5YP'), 'CombineProformaPF-and-5YP'[Date] <= _Date && 'CombineProformaPF-and-5YP'[Attributes] = "5YP Cost" 
                                )
RETURN 
    SWITCH(TRUE(), 
        ISBLANK([cumulativePostings] ), BLANK(), 
        CALCULATE([SumValue], _tbl)
        --CALCULATE(5000, _tbl)
    )

Thanks

[JustDavid]

@sevenhills Thank you! And sorry for the delayed response. Tried adjusting my relationship to look at 'Cost Code' instead of 'Cost Code (RAW)' and adjusted PQ to adjust for nulls.

And using your [5 YP Cost] measure, it works.

Regarding the 5000, it's actually just an imaginary number that I come up with when I try to "paint" the desired solution.

Once again thank you and I've accepted your solution and hopefully it'll bring your reputation up.

[sevenhills]

Thanks and glad to hear the solution worked!  

[sevenhills]

If you agreed to do replace the column data, you can do like this in powerquery "CombineProformaPF-and-5YP":

(last few lines)

...
... 


#"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"Company Code", "Phase", "JDE FY"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns1", each [#"Cost Code (RAW)"], each if Text.Trim([#"Cost Code (RAW)"]) = "" or [#"Cost Code (RAW)"] = null then [#"Cost Code"] else [#"Cost Code (RAW)"]
           , Replacer.ReplaceValue,{"Cost Code (RAW)"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Cost Code (RAW)", type text}})
in
    #"Changed Type1"
 

I cannot test the query as I dont have your data loading files in PQ, so be mindful in adjusting the spelling 🙂