How to create an incremental progress line in Power BI

[ole75634]

Hello, 

    I am tackling an issue that is driving me Mad! I am trying to create essentially a forecast and incremental progress line based on data that is refreshed daily. I generated a cumlative curve using the following measure: 

Cumlative Vendor Data 1 =

VAR _maxdate = Max('Vendor Data Status Report'[SDDC DUE DATE])

RETURN

CALCULATE(COUNT('Vendor Data Status Report'[DOCUMENT TITLE]),ALLSELECTED('Vendor Data Status Report'),'Vendor Data Status Report'[SDDC DUE DATE] <= _maxdate)

Which generates the blue line seent below: 

I want the yellow line to show progress to date and end at todays date. 

 I get the above result when I use this calculation: 

Incremental Vendor Data =

VAR _maxdate = Max('Vendor Data Status Report'[SDDC DUE DATE])

RETURN

CALCULATE(COUNT('Vendor Data Status Report'[DOCUMENT TITLE]),ALLSELECTED('Vendor Data Status Report'),'Vendor Data Status Report'[PDDM UPLOAD DATE] <= _maxdate)

How do I stop the yellow line at todays date? I have tried some different ways but it either makes my incremental line go crazy or flat. Help Please... 

[rajendraongole1]

Hi @ole75634 - You can add a condition to compare each date in the data to today’s date and limit the calculation

Incremental Vendor Data =
VAR _maxdate = MAX('Vendor Data Status Report'[SDDC DUE DATE])
VAR _today = TODAY()
RETURN
CALCULATE(
COUNT('Vendor Data Status Report'[DOCUMENT TITLE]),
ALLSELECTED('Vendor Data Status Report'),
'Vendor Data Status Report'[PDDM UPLOAD DATE] <= _maxdate,
'Vendor Data Status Report'[PDDM UPLOAD DATE] <= _today
)

Above I have modify your DAX measure so that it only includes data up to today

Hope this helps.

[ole75634]

@rajendraongole1 Thank you so much for the quick response. I updated my DAX as suggested and it yielded the same result: 

Incremental Vendor Data =

VAR _maxdate = MAX('Vendor Data Status Report'[SDDC DUE DATE])

VAR _today = TODAY()

RETURN

CALCULATE(COUNT('Vendor Data Status Report'[DOCUMENT TITLE]),ALLSELECTED('Vendor Data Status Report'),'Vendor Data Status Report'[PDDM UPLOAD DATE] <= _maxdate,'Vendor Data Status Report'[PDDM UPLOAD DATE] <= _today)

Do you have any other thoughts? 

[Anonymous]

Hi @ole75634 ,

I think you can try code as below.

Incremental Vendor Data =
VAR _maxdate =
    MAX ( 'Vendor Data Status Report'[SDDC DUE DATE] )
VAR _today =
    TODAY ()
RETURN
    IF (
        _maxdate <= _today,
        CALCULATE (
            COUNT ( 'Vendor Data Status Report'[DOCUMENT TITLE] ),
            ALLSELECTED ( 'Vendor Data Status Report' ),
            'Vendor Data Status Report'[PDDM UPLOAD DATE] <= _maxdate
        )
    )

Best Regards,
Rico Zhou

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

[ole75634]

@Anonymous This is great and gets me alomost there. But I have seem to have one more issue. According to my raw data set I have actually only received 430 items but my dashboard shows that I have received 527 items. Why would this be? Do I need to add a filter command to my dashboard? 

[Anonymous · Accepted solution]

Hi @ole75634 ,

I have checked your original screenshot, it seems that the result of [Incremental Vendor Data] will return 527 as well.

My workaround as above can achieve your goal that remove data after the max date.

Due to I don't know your data model, I will give you some advice.

1. Please check whether there are duplicates [DOCUMENT TITLE] in same month. If yes, I think you need to use DISTINCTCOUNT() instead of COUNT().

Incremental Vendor Data =
VAR _maxdate =
    MAX ( 'Vendor Data Status Report'[SDDC DUE DATE] )
VAR _today =
    TODAY ()
RETURN
    IF (
        _maxdate <= _today,
        CALCULATE (
            DISTINCTCOUNT ( 'Vendor Data Status Report'[DOCUMENT TITLE] ),
            FILTER (
                ALLSELECTED ( 'Vendor Data Status Report' ),
                'Vendor Data Status Report'[PDDM UPLOAD DATE] <= _maxdate
            )
        )
    )

2. Please check whether there are some filters or slicers in your page. If yes, you may try ALLEXCEPT() function or still ALLSELECTED() but add more parameter in your code.

Incremental Vendor Data =
VAR _maxdate =
    MAX ( 'Vendor Data Status Report'[SDDC DUE DATE] )
VAR _today =
    TODAY ()
RETURN
    IF (
        _maxdate <= _today,
        CALCULATE (
            DISTINCTCOUNT ( 'Vendor Data Status Report'[DOCUMENT TITLE] ),
            FILTER (
                ALLSELECTED ( 'Vendor Data Status Report' ),
                'Vendor Data Status Report'[PDDM UPLOAD DATE] <= _maxdate
                    && 'Vendor Data Status Report'[SelectedColumn]
                        = MAX ( 'Vendor Data Status Report'[SelectedColumn] )
...
            )
        )
    )

If this reply still couldn't resolve your issue, please share a sample file with us and show us a screenshot with the result you want.

Best Regards,
Rico Zhou

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

[ole75634]

Hi @Anonymous, 

    Thank you for your response to my query. I took some time in getting back to you due to a holiday. I have sorted out how to get the line to stop at every week ending day now so that is resolved. What is giving me issues now is my results. 

The light blue line is essentially the plan line showing per my schedule the cumulative qty of information I should be receving by week. Which line up perfectly with the source data. The issue I am having now is the incremential line. It is giving me results inconsistent with my actual data I am receving. I have tried multipla ways as the above image shows in calculating the actual data recived and each DAX equation is giving me different results and never lines up with the actual data recieved. 

Here it is in a table format: 

The left column is the qty of information required by the end of each week which lines up perfectly with my source data using the following calculation: 

Vendor Data Plan =

VAR _maxdate = Max('Vendor Data Status Report'[SDDC DUE DATE])

RETURN

CALCULATE(SUMX('Vendor Data Status Report','Vendor Data Status Report'[For Cumulative]),ALLSELECTED('Vendor Data Status Report'),'Vendor Data Status Report'[SDDC DUE DATE] <= _maxdate)

The middle column is supposed to be the incremental data actually received and using a slightly different calcualtion I get a result that does not line up with the source data: 

Vendor Data Plan =

VAR _maxdate = Max('Vendor Data Status Report'[SDDC DUE DATE])

RETURN

CALCULATE(SUMX('Vendor Data Status Report','Vendor Data Status Report'[For Cumulative]),ALLSELECTED('Vendor Data Status Report'),'Vendor Data Status Report'[SDDC DUE DATE] <= _maxdate)

With this calculation it shows I should have recived 682 items when I reality I have actually only received 526. 

The third column is using the calculation you had suggested to try and still I get an inconsistent result: 

Incremental Vendor Data Test =

VAR _maxdate =

    MAX ( 'Vendor Data Status Report'[SDDC DUE DATE] )

VAR _today =

    TODAY ()

RETURN

    IF (

        _maxdate <= _today,

        CALCULATE (

            DISTINCTCOUNT ( 'Vendor Data Status Report'[DOCUMENT TITLE] ),

            FILTER (

                ALLSELECTED ( 'Vendor Data Status Report' ),

                'Vendor Data Status Report'[PDDM UPLOAD DATE] <= _maxdate

            )

        )

    )

Do you have any thoughts on this? Also, in regards to filters, I did remove all filters I am using on my dashboard and still the results are off and way worse in both incremental cases but the plan data always lines up perfectly. So, right now I can get the line to end how I want it too but I cannot get the result of the incremental line to match the source data. This is urgent for me so any help you can provide is much appreciated. Thank you! 

[ole75634]

@Anonymous Trying to reach out to you again. This issue was listed as closed but I am still having issues. Thank you! 

[ole75634]

All, I am looking at my yellow incremental line and what seems to be the issue is that for some reason it starts at a 97 instead of zero and is incorrectly counting the number of documents actually received otherwise known as PDDM upload date. 

So for example the table shows in March of this year It shows we had received 117 documents when in reality we had only received 23.