Forum Discussion

ole75634's avatar
ole75634
Frequent Visitor
1 year ago
Solved

How to create an incremental progress line in Power BI

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... 
 
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

8 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      ole75634
      Frequent Visitor
      1 year ago

      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's avatar
        Anonymous
        Not applicable
        1 year ago

        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.