Forum Discussion

angelina_tr's avatar
angelina_tr
Regular Visitor
2 years ago

Line chart

Hej, for some reason the curve shows the same values ​​for future months for which we have not yet entered data into the Excel. Even there are not even empty cells for this data 

 

In the second picture I applied a filter with the value show less than the number of beneficiaries from the last report. but then Power Bi hides the data for the last month of the report. (I want to see 1,089,988 as last reported data)

I tried to combine data equal to or less than the value, but still with the latest current figures a straight line comes out for future months that I want to hide. Maybe, you know the answer to what to do to hide the direct line for future months? I don't want to see the strikt line with no walue yet. And keep just one month shows the last reported number ? I would be grateful, if you tell any suggestions.

Need Help

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    angelina_tr  This approach will display Sales, for example, only up until the year to date:

    Create a ytd_dates column in your date table (or data table depending on your model):

     
    ytd_dates =
    IF (
        [Month] <= TODAY (),
        1,
        0
    )


    Then create a YTD Sales measure (or Cumulative reach measure in your case):

    YTD Sales =
    CALCULATE (
        TOTALYTD ( SUM ( Sheet1[Sales] ), Sheet1[Month], "12/31" ),
        KEEPFILTERS ( Sheet1[ytd_dates] = 1 )
    )

     

     

     

     

     

     

     

  • angelina_tr's avatar
    angelina_tr
    Regular Visitor
    2 years ago
    Sorry, I did't get it ( 

    In powerBi I have my formula like this: 

    Running total =
    VAR CurrentMonth = MAX ( 'Reach unique'[Month].[Date] )
    RETURN
    CALCULATE (
        SUM ( 'Reach unique'[Value] ),
        FILTER (
            ALLSELECTED ( 'Reach unique'[Month].[Date] ),
            'Reach unique'[Month].[Date] <= CurrentMonth
        )
    )

    I collect my database in Excel file and link it with PowerBi. And I make calculation total already in PowerBi program. I did't understend there I could add the new collumn as you discrubed in a massage. 

    Do you have any saggestions how I could modify my formula ? 

    Many thanks, 
    Anhelina 
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      angelina_tr  Why is your "VAR CurrentMonth" saving the maximum date instead of the current date?

      • angelina_tr's avatar
        angelina_tr
        Regular Visitor
        2 years ago
        Good question! I replase it with "today"
        But now my curve looks like a line ande include future monthes. I have no idea why